Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two lists
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two lists
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 C1:D1 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 --- "John Fesen" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two lists
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 "John Fesen" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two lists
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "John Fesen" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two lists | Excel Discussion (Misc queries) | |||
Comparing to lists | Excel Discussion (Misc queries) | |||
Comparing Lists | Excel Worksheet Functions | |||
Comparing lists | Excel Worksheet Functions | |||
Comparing Lists | Excel Worksheet Functions |