Filtering Data in Columns

[Guest]

I would like to compare the values in two columns in a spreadsheet, and find
the values that are common to both columns, and output those values to a new
column.

 

[T. Valko]

Does one column have more data than the other? (you only need to check the
shortest column)

What type of data are we talking about, text, numbers, both?

Biff

 

[Guest]

Thanks for the reply. Lists are part numbers, number of of entries sometimes
quite long, over 1000 lines. Not sure if Access or Excel has a easy command
to accomplish.

 

[T. Valko]

Ok....

The easiest way to do this would be to use a helper column to mark the
matches then apply a filter then copy the filtered data to where you want
it.

Suppose your data is in the range A2:B1000. A1 and B1 are column headers.

Enter this formula in C2 and copy down to C1000:

=(ISNUMBER(MATCH(A2,B$2:B$1000,0)))*ROW()

Now, select cell A1
Goto the menu Data>Filter>AutoFilter
Click the drop arrow on column C
Select Custom>Is greater than
Enter 0 in the box on the right
OK

Select the visible data from column A and copy/paste it to where you want
it.

Then you can remove the filter and delete the formulas from column C.

Biff