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.
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.
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.
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|
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|
That’s the end of this topic. Hope you enjoy this series.