Search and Combine Data

[estone4009]

Here is what I have:
4 columns of data (50,000 Rows each)

I want something to search each 4 columns of data write a new list(column or worksheet) of the values that appear in all 4 columns.

Any ideas on how I can do this quickly?

 

[jdhilljr]

Insert row a
in a1
=IF(CONCATENATE(B1,VLOOKUP(B1,$C$1:C$50000,1,0),VLOOKUP(B1,$D$1:$D$50000,1,0),VLOOKUP(B1,$E$1:$E$50000,1,0))=CONCATENATE(B1,B1,B1,B1),TRUE,FALSE)

copy down then copy and paste special values only.
Sort by A
True in A means the value in B is in all 4.

Jim

 

[TonyJollans]

Hi estone4009,

Same idea, different (slightly shorter) formula.

Assuming your data is in columns A to D, enter this in E1:

[blue][tt] =NOT(OR(ISNA(MATCH(A1,B:B,0)),ISNA(MATCH(A1,C:C,0)),ISNA(MATCH(A1,D,0))))[/tt][/blue]

and copy down.

Then (unless you already have a header row) add a row before the first, and then ..

Autofilter for TRUE in column E.
Copy the list from column A to wherever you want it

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[estone4009]

I tried the second formula but I need to give you more details. I over guessed on the number of values in my Rows.
Row A: 7109
Row B: 6923
Row C: 13747
Row D: 13973

The formula seemed to work but after Row A and Row B ran out of Data the True and false kept going. Do I need to just swap Row A and D or is there something else I can do?

 

[TonyJollans]

Hi estone4009,

You only need to copy the formula down as far as column A goes. By definition if a value isn't in column A, it isn't in all 4 columns.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.