Comparing two list with similar list of items

November 7th, 2005 by admin admin
Now that you have learnt the required functions for comparing the two lists, we can now show you how to do the comparison.
Assuming you have 2 lists as shown, each residing in one worksheet.
List 1
Excel good
Excel VBA good
SynergyWorks good
Internet Marketing good
List 2
Excel VBA good
Synergyworks good
Adsense good
Adwords good
Both lists have items of their own (Excel, Internet Marketing in list 1 and Adsense, Adwords in list 2) and items that are available to both of them (Excel VBA). You need to compare the 2 lists and find out which item is present in either list or both.
For this to happen, first combine the 2 lists and put them into fresh new worksheet as shown.
Excel VBA
Internet Marketing
Excel VBA
Do a sort so that similar items are listed together. To remove the duplicate items, you must use the If function in the next column. The following table starts from A2 and A1 is a blank cell. Enter the formula“=IF(A2=A1,”Duplicate”,”")” as show in the digram below. Copy the formula down the list.
Adsense  =IF(A2=A1,”Duplicate”,”")
Excel VBA  
Excel VBA Duplicate
Internet Marketing  
SynergyWorks Duplicate
The formula will flag out the duplicates for Excel VBA and SynergyWorks. Do a autofilter using the criteria “blanks” and the duplicates are sieved out.
Highlight the entire list and press Function key F5, go to special cells and select the option visible cells only and click “OK”.
Click on the copy icon or go to the menu bar and click on edit, copy.
Paste the results in a brand new worksheet. You have a list of unique items.
Use vlookup and lookup the value in the repective cells as shown. replace the word “list1″ and “list2″ with the relevant ranges. “=VLOOKUP(lookupcell,list1,2,FALSE)“. Assuming that adsense is in cell A2, then lookup cell is A2.
  List 1 List 2
Adsense #N/A good
Adwords #N/A good
Excel good #N/A
Excel VBA good good
Internet Marketing good #N/A
SynergyWorks good good
To beautify the results, you could extend the formula using if function —>“=IF(ISNA(VLOOKUP(B2,Sheet1!$B$2:$C$5,2,FALSE))=TRUE,”",1)” It means that if the result is “#NA”, show a blank cell “”, else show the number 1. ANd you have got a table which show which word/phrase exist in which table as show below.
  List 1 List 2
Adsense   1
Adwords   1
Excel 1  
Excel VBA 1 1
Internet Marketing 1  
SynergyWorks 1 1
That’s the end of this topic. Hope you enjoy this series.

Leave a Reply

You must be logged in to post a comment.