How to Extract Numbers From a Table List

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

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...

I bet DR. Math knows this...

http://mathforum.org/library/drmath/sets/high_puzzles.html

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

Thank you for help

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?

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

I'm obsessive

Frustrated I didn't understand the problem

"I didn't understand the problem"

I didn't understand the question.

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)

Keep going maybe a pattern will emerge...

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

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.

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

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?

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?

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.

I will analyze this and try to figure it out

Thanks...

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

^{Yeah, thas it - sic em on Lyn!}

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

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.

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

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.

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

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"

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.

Time for a flow chart!

You think ?

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.

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.

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

"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