Previous in Forum: Water Level Sensing Equipment   Next in Forum: Need a book
Close
Close
Close
33 comments
Associate

Join Date: Jul 2008
Posts: 50

How to Extract Numbers From a Table List

12/17/2011 10:25 AM

Hello everybody,

I have one big/small problem with extract the right numbers from a table which has 208589 rows. The internal parameters are these: we have numbers that has 6 places (xxxxxx) and is filed with the numbers from 1 to 9.

I want that the number has the difference 2 in one or more places. Example:

112647 ; these number is GOOD, because let say that we choose the starting point

112648 ; these number is NOT GOOD, because it has from the above number in last

place the degree 1 (8-7=1)

221658 ; these number is also NOT GOOD, because it has from the first number in

first and second place degree 1 (2-1=1) in third and fifth and sixth places it

has also degree 1

112667 ; these number is GOOD because it has in fifth place degree 2 (6-4=2)

212556 ; these number is NOT-GOOD, because if we than take the above number as

GOOD, than this one is not, because it has from the number above in

first, fourth, fifth and sixth places degree 1

Another example from a table:

Does somebody know how to solve this, because I am not good in mathemathics and programming so that I can write a macro that will do this calculation faster than me.

Thank you and have a nice day

Register to Reply
Pathfinder Tags: combination Excel macro math variation
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Guru

Join Date: Oct 2008
Posts: 42293
Good Answers: 1663
#1

Re: How to extract numbers from a table list

12/17/2011 10:33 AM

Reduce your table by 208587 rows and you'll be fine.

Register to Reply Score 1 for Off Topic
Associate

Join Date: Jul 2008
Posts: 50
#2
In reply to #1

Re: How to extract numbers from a table list

12/17/2011 10:50 AM

Hehe.. ok this is one "solution"

the solution is simple but we people like sometimes to complicate things so that we use imagination to solve the problem...

Register to Reply
Guru

Join Date: Mar 2007
Location: at the beach in Florida
Posts: 31570
Good Answers: 1744
#3

Re: How to extract numbers from a table list

12/17/2011 11:10 AM
__________________
Break a sweat everyday doing something you enjoy
Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#4
In reply to #3

Re: How to extract numbers from a table list

12/17/2011 11:30 AM

We will see if he knows :) or he knows but have not so much time for this :)

Thank you for help

Register to Reply
Guru
Engineering Fields - Systems Engineering - New Member Popular Science - Weaponology - New Member

Join Date: Jun 2006
Location: Borrego Springs
Posts: 2636
Good Answers: 62
#5

Re: How to extract numbers from a table list

12/17/2011 11:50 AM

Going back to your example

Row 10 is GOOD because NOT GOOD are excluded?

So row 10 is good because it meets the criteria compared to row 4? The last GOOD row?

__________________
"If you want to get somewhere else, you must run at least twice as fast as that!"
Register to Reply
Guru

Join Date: Oct 2008
Posts: 42293
Good Answers: 1663
#6
In reply to #5

Re: How to extract numbers from a table list

12/17/2011 11:57 AM

You actually read that stuff???????????

Register to Reply
Guru
Engineering Fields - Systems Engineering - New Member Popular Science - Weaponology - New Member

Join Date: Jun 2006
Location: Borrego Springs
Posts: 2636
Good Answers: 62
#7
In reply to #6

Re: How to extract numbers from a table list

12/17/2011 12:00 PM

I'm obsessive

Frustrated I didn't understand the problem

__________________
"If you want to get somewhere else, you must run at least twice as fast as that!"
Register to Reply
Guru

Join Date: Oct 2008
Posts: 42293
Good Answers: 1663
#10
In reply to #7

Re: How to extract numbers from a table list

12/17/2011 12:08 PM

"I didn't understand the problem"

I didn't understand the question.

Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#9
In reply to #5

Re: How to extract numbers from a table list

12/17/2011 12:07 PM

Yes row 10 is GOOD becase of the criteria which is degree or difference is 2

The last good row is not the criteria (here I filled some other good/bad numbers), the row 17 is excluded because of the row 11 (see picture below)

Register to Reply
Guru

Join Date: Mar 2007
Location: at the beach in Florida
Posts: 31570
Good Answers: 1744
#11
In reply to #9

Re: How to extract numbers from a table list

12/17/2011 12:57 PM

Keep going maybe a pattern will emerge...

__________________
Break a sweat everyday doing something you enjoy
Register to Reply
Guru
Engineering Fields - Systems Engineering - New Member Popular Science - Weaponology - New Member

Join Date: Jun 2006
Location: Borrego Springs
Posts: 2636
Good Answers: 62
#8

Re: How to extract numbers from a table list

12/17/2011 12:05 PM

So answers to your problem are all over:

http://en.allexperts.com/q/Excel-1059/COMPARING-CELLS.htm

http://uk.answers.yahoo.com/question/index?qid=20061012025716AAKRQvo

http://excel.bigresource.com/Compare-the-1st-9-digits-in-two-columns-looking-for-duplicates-rzDnZokA.html

The differences in approach are based on whether you choose to treat the 'strings' as numbers or 'characters', either will work

__________________
"If you want to get somewhere else, you must run at least twice as fast as that!"
Register to Reply
Guru
Technical Fields - Technical Writing - New Member Engineering Fields - Piping Design Engineering - New Member

Join Date: May 2009
Location: Richland, WA, USA
Posts: 21005
Good Answers: 781
#12

Re: How to Extract Numbers From a Table List

12/17/2011 7:21 PM

I think the language of the OP is too unclear to tell what is to be done. The good/not good decisions given seem not to conform to the given language.

__________________
In vino veritas; in cervisia carmen; in aqua E. coli.
Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#13

Re: How to Extract Numbers From a Table List

12/18/2011 6:49 AM

I will try to explain it in some other way...

I need that computer looks in next row if it match the criteria in every OK upper rows and marks me with OK. The criteria is the difference in just one column between every OK rows must be 2 (it's not important in which column, only one column must match the criteria).

The first row is the start point for every next rows

Register to Reply
Guru
Technical Fields - Technical Writing - New Member Engineering Fields - Piping Design Engineering - New Member

Join Date: May 2009
Location: Richland, WA, USA
Posts: 21005
Good Answers: 781
#14
In reply to #13

Re: How to Extract Numbers From a Table List

12/18/2011 7:10 AM

Huh?

Are you comparing each row with the first row, or with the immediately preceding row, or something else?

In this particular case, why are you comparing the 16th row with the 10th row?

__________________
In vino veritas; in cervisia carmen; in aqua E. coli.
Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#15
In reply to #14

Re: How to Extract Numbers From a Table List

12/18/2011 8:33 AM

I'm comparing next row with the previous OK rows.

For the second question I have just pointed out an example that I'm not comparing only the last OK row but the whole upper rows that are OK.

If I use only column A and make this criteria, than use column B and make same criteria and etc.... it's like to use one column at a time and with "superposition" merge together... do You think it will work?

Register to Reply
Guru

Join Date: Dec 2010
Location: North West England
Posts: 1170
Good Answers: 153
#16

Re: How to Extract Numbers From a Table List

12/18/2011 11:39 AM

Your criteria for 'good' does not look at six digit numbers but treats each column as a separate entity. Have the computer look at digits in the first column only and store only those numbers that occur. In the sample shown only 1 occurs but in the whole list maybe 1,2,3,7 & 8 are present in the first column. Of these only the pair 1&3 fit your criteria so any number with 1 or 3 in the first column is 'good'. Repeat for the second column and the result might be 1,2,3,4,6,8 & 9. The pairs 1&3, 2&4, 4&6, 6&8 fit your criteria. So any number with 1,2,3,4,6 or 8 in the second column meets your criteria and is 'good'. Repeat for the other columns. Now compare your list against 1,3 in the first column, 1,2,3,4,6,8 in the second column, etc. If the digit in any column matches, copy that number into a 'good' list and move on to the next row. Note if any column has all nine numbers present, all the list is 'good'

As a programming tip, treat each number as a string and that will allow you to pick out individual digits. The whole program should scan the list 6 times to pick out the numbers on which the correct criteria pairs are based and, if the numbers are random, the equivalent of three times (average based on the hits being equally distributed in each column) to pick out the 'good' numbers. The only hard bit is deciding which numbers in each column constitute a pair. If you do this step manually the remainder of the program consists of if-then compare functions and loops and a counter to increment from the first to subsequent columns.

Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#20
In reply to #16

Re: How to Extract Numbers From a Table List

12/18/2011 2:27 PM

I will analyze this and try to figure it out

Thanks...

Register to Reply
Guru
Popular Science - Cosmology - Let's keep knowledge expanding Engineering Fields - Retired Engineers / Mentors -

Join Date: Dec 2006
Location: North America, Earth
Posts: 4390
Good Answers: 104
#17

Re: How to Extract Numbers From a Table List

12/18/2011 12:45 PM

Perhaps if you told us what you are trying to accomplish, we Lyn could give you some assistance.

__________________
“I would rather have questions that can't be answered than answers that can't be questioned.” - Richard Feynman
Register to Reply Score 1 for Good Answer
Guru
Engineering Fields - Systems Engineering - New Member Popular Science - Weaponology - New Member

Join Date: Jun 2006
Location: Borrego Springs
Posts: 2636
Good Answers: 62
#18
In reply to #17

Re: How to Extract Numbers From a Table List

12/18/2011 1:05 PM

Yeah, thas it - sic em on Lyn!

__________________
"If you want to get somewhere else, you must run at least twice as fast as that!"
Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#19
In reply to #17

Re: How to Extract Numbers From a Table List

12/18/2011 2:26 PM

ammmm... hmmm... I want from this table which has approx. 200000 rows(numbers), exclude this numbers which don't match some criteria. The criteria is - the number from column A or B or C or D or E or F must have from other numbers difference 2)

Maybe now has some sense... apologyze for my "different" interpretation of the problem

Register to Reply
Guru
Popular Science - Cosmology - Let's keep knowledge expanding Engineering Fields - Retired Engineers / Mentors -

Join Date: Dec 2006
Location: North America, Earth
Posts: 4390
Good Answers: 104
#25
In reply to #19

Re: How to Extract Numbers From a Table List

12/19/2011 7:15 PM

OK, I think a good number is any one that has any digit that is off by 2 from the corresponding digit of any good number. Is that correct? You will have to store each number in a file, then check each digit of each test number against all numbers in the file (good numbers) to see if there is a difference of 2 on any digit. If one is found, then that number is good and can be added to the file.

You still haven't told us what the list is now, and what the new list will be. That is what I meant by "trying to accomplish". Someone may have a whole better solution that the "off by two" thing you are doing.

__________________
“I would rather have questions that can't be answered than answers that can't be questioned.” - Richard Feynman
Register to Reply
Guru

Join Date: Oct 2008
Posts: 42293
Good Answers: 1663
#23
In reply to #17

Re: How to Extract Numbers From a Table List

12/19/2011 10:29 AM

Me?????? I can't even balance a checkbook.

Register to Reply
Anonymous Poster #1
#24
In reply to #23

Re: How to Extract Numbers From a Table List

12/19/2011 10:54 AM
Register to Reply
Guru

Join Date: Jun 2011
Location: Phnom Penh
Posts: 4019
Good Answers: 102
#21

Re: How to Extract Numbers From a Table List

12/19/2011 12:45 AM

Try explaining the criteria again.

Don't use the word "number" if you mean "digit" and describe the relationship to "place" if it matters. Also explain the criteria for determining which numbers or digits are compared.

I thought I knew what you wanted but now you've lost me totally.

Somebody else asked what you are trying to achieve and you just paraphrased what you thought the algorithm was instead of explaining the point of the exercise.

I sense a language issue here. Walk us though it again please.

__________________
Difficulty is not an obstacle it is merely an attribute.
Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#32
In reply to #21

Re: How to Extract Numbers From a Table List

12/21/2011 2:05 PM

Lets go like this...

We have one column with numbers (112134, 112135, 112136...). If we devide every number in each new column will be the list shown upwards. Lets make the first number as a reference point and mark it as good.

First we devide numbers in columns, so we have 6 column, because we have six digit's.

xxxxxx A B C D E F

row 1: 1 1 2 1 3 4

row 2: 1 1 2 1 3 5

row 3: 1 1 2 1 3 6

row 4: 1 1 3 3 2 5

Now comparing columns between the second and forst rows:

Column A: 1-1=0 doesn't match criteria

Column B: 1-1=0 doesn't match criteria

Column C: 2-2=0 doesn't match criteria

Column D: 1-1=0 doesn't match criteria

Column E: 3-3=0 doesn't match criteria

Column F: 5-4=1 doesn't match criteria

So this second row is not good and must be excluded from the list

Comparing third and first row(second was excluded from the list):

Column A: 1-1=0 doesn't match criteria

Column B: 1-1=0 doesn't match criteria

Column C: 2-2=0 doesn't match criteria

Column D: 1-1=2 doesn't match criteria

Column E: 3-3=0 doesn't match criteria

Column F: 6-4=1 this row match my criteria and it is on my good list

Comparing fourth with the first and third rows(second was excluded from the list):

First compare fourth with the first row:

Column A: 1-1=0 doesn't match criteria

Column B: 1-1=0 doesn't match criteria

Column C: 3-2=1 doesn't match criteria

Column D: 3-1=2 match my criteria And is good(no need to go ahead)

Let' move on to compare it the fourth and the third row(the good one):

Column A: 1-1=0 doesn't match criteria

Column B: 1-1=0 doesn't match criteria

Column C: 3-2=1 doesn't match criteria

Column D: 3-1=2 which match my criteria and no need to check the last two column. This number is Ok and is in good list

Now comparing the fifth row with the good rows(the bad rows were excluded from the list)...

this is the theory that I want to achieve for complete table

"Somebody else asked what you are trying to achieve and you just paraphrased what you thought the algorithm was instead of explaining the point of the exercise."

I didn't really understand what do you mean with that... Did you mean "where to use this in real life?" or something else...

I am trying to explain it as good as possible, but I see that it's also this a challenge

Register to Reply
Guru

Join Date: Feb 2008
Location: Van Nuys, CA
Posts: 563
Good Answers: 33
#33
In reply to #32

Re: How to Extract Numbers From a Table List

12/21/2011 9:41 PM

simke,

Please read, or at least look at, "How to Develop & Use Complex Spreadsheets"
(http://www.ehow.com/how_7721939_develop-use-complex-spreadsheets.html).

Take note of step number one: "Create a Flow Chart"

Register to Reply Score 1 for Off Topic
Participant

Join Date: Dec 2011
Posts: 2
#22

Re: How to Extract Numbers From a Table List

12/19/2011 10:24 AM

Maybe it would work to turn the problem around. Since you have rules for what are good numbers, build a list of allowable numbers based on the first number in your original list. Then, when given a number to check for validity just see if it exists in your "Valid number" list.

Register to Reply
Guru

Join Date: Feb 2008
Location: Van Nuys, CA
Posts: 563
Good Answers: 33
#26

Re: How to Extract Numbers From a Table List

12/19/2011 8:53 PM

Time for a flow chart!

Register to Reply
Guru

Join Date: Jun 2011
Location: Phnom Penh
Posts: 4019
Good Answers: 102
#27
In reply to #26

Re: How to Extract Numbers From a Table List

12/19/2011 10:15 PM

You think ?

__________________
Difficulty is not an obstacle it is merely an attribute.
Register to Reply
Guru
Technical Fields - Technical Writing - New Member Engineering Fields - Piping Design Engineering - New Member

Join Date: May 2009
Location: Richland, WA, USA
Posts: 21005
Good Answers: 781
#28

Re: How to Extract Numbers From a Table List

12/20/2011 2:12 AM

In several posts, there is fine print or blurred colors, making them unreadable. (This seems to be a weakness of the graphics here.)

At least the original post is mostly visible.

Given that only 112135 and 112137 are good so far, how does 112149 become good when column E differs by only 1? If any single digit must differ by 2, can the other digits be anything? I don't think this has been explained (at least not visibly).

How about this? Classify the original number as OOEOEO (i.e., odd odd even odd even odd). Then any other OOEOEO number can be made good by suitable steps of 2 in each of its digits. Is that what you mean?

Hint: when posting graphics, please look at them to see if they are readable.

__________________
In vino veritas; in cervisia carmen; in aqua E. coli.
Register to Reply
Guru
Popular Science - Cosmology - Let's keep knowledge expanding Engineering Fields - Retired Engineers / Mentors -

Join Date: Dec 2006
Location: North America, Earth
Posts: 4390
Good Answers: 104
#29
In reply to #28

Re: How to Extract Numbers From a Table List

12/20/2011 7:47 PM

It would be better if the OP answered this, but he seems to be hiding.

Given that only 112135 and 112137 are good so far, how does 112149 become good?

If my understanding is correct, it's because 9 differs from 7 by 2 (corresponding digit). The good list will be very short compared to the 200,000 numbers. I can't see any possible use for this.

__________________
“I would rather have questions that can't be answered than answers that can't be questioned.” - Richard Feynman
Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#30
In reply to #29

Re: How to Extract Numbers From a Table List

12/21/2011 1:30 PM

Lets go step by step

"Given that only 112135 and 112137 are good so far, how does 112149 become good?"

This is good. Why?

The difference between 112135 and 112137:

it in the last number(digit) and is 2 (7-5=2). This is OK

The difference 112149 is also good. Why?

If we sort complete numbers like this like this and devide every number in column

112135

112137

112149

the difference is in 5th column (4-3=1) which doesn't match my criteria BUT in last sixth column is the difference 2(9-7=2), which makes this row good.

If we compare the third and first row:

You can see that the difference is in second column (4-3=1) which doesn't match my criteria but in last(sixth) column the number is (9-5=4) which match my criteria (the difference is 2 or more than 2).

When we analyze the next complete row(number) it should match the criteria for the previous rows.

Do you understand me? it is a little be tricky

Register to Reply
Associate

Join Date: Jul 2008
Posts: 50
#31
In reply to #28

Re: How to Extract Numbers From a Table List

12/21/2011 1:36 PM

"Given that only 112135 and 112137 are good so far, how does 112149 become good when column E differs by only 1? If any single digit must differ by 2, can the other digits be anything?"

The criteria is in for one column (the function OR) not for all columns. Only one column must match the criteria.

For second question: the steps are similar that mister "jhhassociates" post it here. He give me something to think and I think it will work... I am still analyzing and hope to have some more time to finish this this year.

I will let you know how I have finish this... if I will

Register to Reply
Register to Reply 33 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Copy to Clipboard

Users who posted comments:

Anonymous Poster (1); edignan (4); jhhassociates (1); jhmac (1); lyn (4); pantaz (2); simke (10); SolarEagle (2); StandardsGuy (3); Tornado (3); Wal (2)

Previous in Forum: Water Level Sensing Equipment   Next in Forum: Need a book

Advertisement