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

 

[Guest]

what you want to use is the vlookup. In the function wizard or help, look up
the function VLOOKUP or HLOOKUP, depending on whether the lists are
horizontal or vertical. I would suggest something like this:
=IF(ISERROR(VLOOKUP([reletive cell reference],[absolute
range],1,FALSE)),"Delete Row","Do Nothing")
If you use this function in a column adjacent to the long list, the value
Delete Row will appear next to those rows in the long list which do not
appear in the short list. You could use VBA to automate the deletion of
these rows, but since you are a beginer it'll be easier for you to do
manually.