Filtering Data in Columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

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