Subtracting one column from another

[Brian Bagnall]

Hi,

I have two columns of data and I want to remove any names that appear
in another column. For example:

Column 1:

Bill
Henry
Ruby

Column 2:
Frank
Bill
Joe

I want the calculation to subtract any names that appear in Column 2
to produce the following:

Henry
Ruby

Please let me know what I have to do, possibly with an SQL statement,
to produce this including naming worksheets if thats necessary.

- Brian

 

[Norman Jones]

Hi Brian,

See Chip Pearson's Duplicates page at:

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

See particularly, the section entitled:

'Extracting Values On One List And Not Another'

 

[Max]

Another play to try, using non-array formulas ..

Assume data is in cols A and B, from row1 down

Bill Frank
Henry Bill
Ruby Joe

Put in D1:
=IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW())

Put in C1:
=IF(ISERROR(SMALL(D,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(D,ROW(A1)),D,0)))

Select C11, copy down until the last row of data in col A

Col D will return the desired results, neatly bunched at the top, viz.:

Henry
Ruby

Freeze the results with an in-place:
Copy > Paste special > Values > OK

--