CODEWITHSUNDEEP
excelcomparevlookup
theHermes
theHermes

Reputation: 23

comparing two columns in excel (VLOOKUP not working)

I have been breaking my head with this problem since morning and I haven't found a solution. Please give your valuable pointers if possible, so that I can try to find the solution.

I basically have two sets of data- an old list and a new list. I wish to compare the new list( comparing name and country together) with an old list, since the new list has a few additional entries. Later on, I would like to create a new list with common entries from both old and new list and add all the new entries below the common ones ( if possible, else I will do that manually later on but I would like Excel to tell me that this is a new entry). Sorry, if this has not been well explained, but maybe the following illustration helps

 Old List           
 Item No. Name          Country

 1  Apples          Italy   
 3  Banana          Spain   
 4  Grapes          Slovakia    
 5  Pineapple   Greece  
 8  Banana          Czech Republic  
 14 Apples          India   
 23 Pineapple   Hungary 
 19 Peach           USA 
 2  Strawberries    France  

 New List       
 Item No. Name       Country

 4  Grapes       Slovakia   
Mango        Pakistan   
 14 Apples       India  
Oranges      Mexico 
 19 Peach        USA    
 2  Strawberries France 
 1  Apples       Italy  
 3  Banana       Spain  
 23 Pineapple    Hungary    
Avocado      Netherlands    

 Expected Output:       
 List with common serial No.s based on common names from both lists

 Item No.Name           Country

 4  Grapes          Slovakia
 14 Apples          India
 19 Peach           USA
 2  Strawberries    France
 1  Apples          Italy
 3  Banana          Spain
 23 Pineapple   Hungary
Mango           Pakistan
Oranges         Mexico
    Avocado         Netherlands

As can be seen in this attachment, I have an old list with Item No., Name and Country. Let's assume that the item numbers have been classified based on some code words. In the second list, there are again Item No.s, Name and Country but some item numbers haven't been filled ( since they are new and have not yet been sorted). Now, I want Excel to compare the names AND countries of both data and provide the common Item No. output if there is a match. If there is no match, then I would like Excel to tell me that this is a new entry. I looked up on various forums and I realized that VLOOKUP command only allows me to search on Name OR Country would give me the common entries of Names/Countries respectively but not Item No.s. Is there any formula that could help me solve this problem?

Upvotes: 0

Views: 744

Answers (2)

MP24
MP24

Reputation: 3200

Just paste the list together, then sort it, and then remove the duplicates. Removing duplicates is built-in into Excel starting from version 2007, you will find it in the Data ribbon (see http://office.microsoft.com/en-001/excel-help/filter-for-unique-values-or-remove-duplicate-values-HP010073943.aspx).

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27478

To use VLOOKUP just concatenate Name and Country, for example, B2 & "-" & C2. You can then do a lookup on the concatenated values in your source table:

VLOOKUP(B2 & "-" & C2,NewList!D2:E100,2,False)

This assumes that the concatenated column is in D in your new table, and that you've copied the numbers to column E (VLOOKUP can't look to the right). I put in the dash for readablility and to avoid the chance that a Country ends with a number, unlikely as that might be.

Upvotes: 0

Related Questions