Comparing two lists

[John Fesen]

I have 2 lists of products on the same spreadsheet - one with 800 rows and
the other with 500 rows. Each list has product descriptions and UPC numbers.
I want to compare list 1 with list 2 and have a new list that only has the
UPC numbers that appear in both lists.

How do I do that?
John

 

[Max]

List 1 in A1 down, List 2 in B1 down
In C1: =IF(COUNTIF(B:B,A1),ROW(),"")
In D1: =IF(ROW()>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW())))
Copy C11 down to the last row of data in col A. Minimize col C. Col D
returns the required results, neatly packed at the top.

Test it out. Should work ok. Click YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Joel]

The best way without using a macro is the following.

1) On a new sheet put the UPC number from the first sheet. then copy the
UPC numbers from the ssecond sheet on the new sheet in the same column after
the UPC numbers from the 1st sheet. You should have the combined list in one
column on the new sheet.

2) Highlight the combined list with mouse. Then go to menu Data - filer
Advance Filter. Select Copy to New Location and check the Unique box, and
place in new location. Now you have a list of unique code.

3) We are now going to make a 4 column Table (Columns D - G) with the
following

a) Column 1 (D) the UPC numbers
b) Column 2 (E) a true or false if the UPC number are on the 1st sheet

=if(Countif(Sheet1!A1:A1000,D1)>=1,True,False)

copy formula down column E

c) Column 3 (F) a true or false if the UPC number are on the 2nd sheet

=if(Countif(Sheet2!A1:A1000,D1)>=1,True,False)

copy formula down column F

d) Column 4 (G) will contain a true if both column 2 and 3 are true.

=AND(E1,F1)

Copy down column G

 

[Don Guillett]

Longest list in col A. Short list in col B. Matches in col C on same row as
search number

Sub ifinb()
For Each c In Range("a2:a6")
Set mfind = Columns("B").Find(What:=c, After:=Range("b1"), _
LookIn:=xlvalues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If Not mfind Is Nothing Then c.Offset(, 2) = c
Next c
End Sub