Comparing two lists
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
"Pauli Soininen" wrote in message
...
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.
|