Searching through 200,000+ records in 2003

[Jeffrey Green]

I have a list of about 20,000 Code on my first worksheet.
I have about 200,000 codes on my second worksheet, 50k to a column, 4 columns.

I need to see if my 20,000 code on on the second sheet.

I would normally cheat and do it the "poor man's way" and name each column on sheet 2 and search all of columns individually.

Gots to be an easier way . . .

thanks

 

[kpark91]

Easiest to hardest implementation algorithms:
1) Sort (Fast)
2) Dictionary (Slow)
3) Hash table (faster)

 

[Aladin Akyurek]

Which sheets? Which columns? Are those four 50k columns sorted in ascending order?

 

[Jeffrey Green]

I can sort all columns as needed . . . .

 

[Aladin Akyurek]

I can sort all columns as needed . . . .

OK. Sheet1 which column/range? Sheet2 which columns/ranges?

 

[Jeffrey Green]

Sheet 1, Column A only, I can name it "source" . . .
Sheet 2, Columns A-D, I can name the range as "data" if it helps.

Tks

 

[Aladin Akyurek]

Sheet 1, Column A only, I can name it "source" . . .
Sheet 2, Columns A-D, I can name the range as "data" if it helps.

Tks

When we sort columns A-D, what do we get:

1) B follows up A alphabetically, C follows up B, D follows up C;
2) Each column is alphabetically independent from others?

 

[Jeffrey Green]

Column A would be, for example A-F, top down
Column B would G-J, top down
Column C would be K-O,
etc.

 

[Aladin Akyurek]

Column A would be, for example A-F, top down
Column B would G-J, top down
Column C would be K-O,
etc.

Great, thanks.

Sheet1, B2, copy down:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5},0,
   (LOOKUP(A2,Sheet2!A:A)=A2)+0,(LOOKUP(A2,Sheet2!B:B)=A2)+0,
   (LOOKUP(A2,Sheet2!C:C)=A2)+0,(LOOKUP(A2,Sheet2!D:D)=A2)+0))

which requires sorting in ascending order.

Unsorted...

=ISNUMBER(LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4},
   MATCH(A2,Sheet2!A:A,0),MATCH(A2,Sheet2!B:B,0),
   MATCH(A2,Sheet2!C:C,0),MATCH(A2,Sheet2!D:D,0))))+0

A result of 1 means: Yes, A2 is in the set, 0 not.

Note that the first option is expected to be faster/more efficient.