Formal to compare a list

[Ange]

hello all,

I need a formula to compare account numbers in two lists, then to
create a third based on what account numbers match.

The whole row (other fields) need to be brought to the third list as
well.

Thanks you

 

[Guest]

Is this what you want?

Sub CompareLists()

Set rnga = Range("List1") ' Contains first list of account numbers
Set rngb = Range("List2") ' Contains second list of account numbers

Set wsin = Worksheets("sheet1") ' input data i.e. contains rows to be copied
Set wsout = Worksheets("sheet2") ' output data (third list)

rw1 = 1
For Each cell In rnga
If Not IsError(Application.Match(cell.Value, rngb, 0)) Then ' matched ....
wsin.Range("A" & cell.Row).EntireRow.Copy Destination:=wsout.Range("A" &
rw1)
rw1 = rw1 + 1
End If
Next

End Sub


[Assume wsin contains one of your account lists].

HTH

 

[Ange solorza]

Thank you for your help.

I am getting at this stage:

wsin.Range("A" & cell.Row).EntireRow.Copy Destination:=wsout.Range("A" &
rw1)

I don't understand.

 

[Guest]

This copies the entire row when accounts matched from the "input" list in
Sheet1 to the "output" list in Sheet2.

List1 accounts (account number in column A)
Row A B,C ..........
1 123 ....+ other data (Columns)
2 246.... + other data (Columns)
3 397... + other data (Columns)

List2 Accounts
A
123
397
518

Accounts 123 and 397 match so rows 1 and 3 from the List1 data are copied to
your list3 which I assumed to be in aother sheet. So Sheet2 will contain:

List3

123 ........ row 1
397 ........ row 2

Hope this makes sense (as I don't know exactly how your data (lists) are
organised).