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 |
| Excel VBA |
| SynergyWorks |
| Internet Marketing |
| Excel VBA |
| SynergyWorks |
| Adsense |
| Adwords |
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","") |
| Adwords | |
| Excel | |
| Excel VBA | |
| Excel VBA | Duplicate |
| Internet Marketing | |
| SynergyWorks | |
| 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.
No comments:
Post a Comment