Comparing two lists

[Pauli Soininen]

I have two lists, a long list like:

1 itemA
2 itemB
4 itemC
5 itemD

and a short list like:

2 null
4 null

I'm looking for a method that would result in a chopped long list like this:

2 itemB
4 itemC


So, I'd like to automatically compare the lists and as the result remove all
rows from the long list that contain a number in the leftmost cell that is
not present in the leftmost cells of the short list.

In other words, I'd like to remove the row with itemA and the row with itemD
based on the fact that those rows are not present on the second list. (Note
that the number is a value in a cell, not a row number.) How could I manage
this? I'm new with Excel so newbie explanation is appreciated. It doesn't
have to be done in Excel but I thought Excel might be good at this.

 

[Tom Ogilvy]

use countif

C1 of the long list

=if(countif(Sheet2!$A:$A,A1)>=1,na(),"")

then drag fill down the column

select C do Edit=>goto Special and select formulas and Errors, then do
Edit=>Delete and choose entire row.

Delete column C

in code

Dim rng as Range, rng1 as Range
With Worksheets(Sheet1)
set rng = .Range(.Cells(1,1),.Cell(rows.count,1).end(xlup))
End with
rng.offset(0,2).Formula = "=if(countif(Sheet2!$A:$A,A1)>=1,na(),"""")"
On Error Resume Next
set rng1 = rng.offset(0,2).specialCells(xlFormulas,xlErrors)
On Error goto 0
if not rng1 is nothing then
rng1.EntireRow.Delete
end if

--
Regards,
Tom ogivy


then drag fill down the column