0

I have an excel sheet with two datasets and each of them having two columns namely MinistryName and Revenue. I need to match MinistryName in Dataset-1 with that in Dataset-2 and note the revenue.

First I thought it was easy and I can use vlookup to grab all the values from second dataset but then I realized that there is slight difference in names. So vlookup didn't work. Take a look at the below sample values from two datasets.

Dataset-1                                         Revenue    
-----------------------------------------------------------
High Office of Anti Corruption                    78.67
Central Statistics organization                    6.56
National Academy of Sciences                      54.21


Dataset-2                                         Revenue    
-----------------------------------------------------------
The High office of Oversight and Anti Corruption  86.00  
Central Statistics Office                         12.40
Science Academy                                   75.91

There is a lot of data in spreadsheet having similar values in Name column. I know that for example;
Central Statistics organization is the same as Central Statistics Office logically but how i can compare them in excel. Using vlookup excel will always assume that they are different.

Is there any string comparision function in excel by which i can compare keywords or compare two strings for similarity? The need is that I want to pick Revenue values from Dataset-2 and put them after Revenue Column in Dataset-1. It will also be useful if any one has solution using VBA function.

asim-ishaq
  • 2,190
  • 5
  • 32
  • 55
  • 1
    Actually this comparison is for Ministry datasets. I have many other units to compare as well (Unit, District, Customs etc). The description often changes from year to year in our government chart of accounts. The two datasets in this spreadsheet contains 720 records for 5 years. Technically there should not be any name new in either datasets. – asim-ishaq Aug 14 '13 at 14:39
  • 1
    This thread may be useful http://stackoverflow.com/questions/5859561/getting-the-closest-string-match –  Aug 14 '13 at 16:01

1 Answers1

1

The MS fuzzy look up may be what you need here. This allows for "fuzzy" searches which is those that essentially are within a certain distance of differences from each other.