3

Here is the scenario:

On column A each cell has random text within it (names, address, account numbers, etc). Column A extends down to over 40 thousand rows. On column B I have a list of (account) numbers. Column B extends down to about 5 thousand rows.

I need to filter column A to show only the rows that contain the account numbers that are part of column B.

Neither column A nor B are in any specific order.

I think he above scenario speaks for itself, but I can provide a quick example if needed (don't want to over complicate it).

  • 2
    Check out Excels Conditional formatting feature. You can do a FIND formula there and only display the rows you suggest or just color them... – jtheman Dec 01 '12 at 23:54

1 Answers1

1

Inelegant but effective: Insert a column between your A and B columns, with 

=VLOOKUP(A1,C$1:C5000,1,FALSE)

at the top and autofilled down. Then filter all, with that column deselecting #N/A.

Note '5000' above should be last occupied row number of your existing Column B.

pnuts
  • 6,072
  • 3
  • 28
  • 41
  • 1
    pnuts, the formula you provided works when A1 EQUALS one of the values in column C. But in my case, A1 CONTAINS one of those values. That's what's giving me a hard time. –  Dec 02 '12 at 19:18
  • That is brilliant! I understand your caveat, but your formula worked like a charm for what I need. Thank you! –  Dec 02 '12 at 22:27