Archive for July 17th, 2013

Excel Tip: Compare two lists (Method 1) July 17th, 2013

Vinod Kumar

One of my friend recently had this requirement where he wanted to compare two lists and find out which were the values from second list that are not in first list. This was an interesting requirement and we kept playing with Excel to come up with close to 3 different methods. After that task was complete, I thought it is worth every bit to bring all these methods over my blog for future reference. So have fun !!!

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function

Method 3: Using PIVOTTABLE.

Let us look at the dataset in hand. It is a list of companies with some random revenue numbers listed for two years. Interestingly there are differences in both the dataset. Now the task is simple. We want to check companies in “List B” which are not in “List A”. So let us get started.

As mentioned earlier, the Method 1 is to use MATCH() function. Here is a neat trick, when you type “=MATCH(“ –> Press “CTRL+A”.

Voila, this is the magic dialog we get. This will guide us for various parameters for that given function. This trick can be done with any function which has parameters values.

Lookup Value => in our data set it is the first cell D3 from List B.

Lookup array => in our data set it will be A3:A10. Please note that we will use the same range and as we move through the list of companies. Hence press F4 to get $A$3:$A$10. This is an very important step.

Match_Type => In our example we will have 0 for exact match.

Press “OK” now.

Now we will use the cross arrow trick we used before. Move to right corner of this cell till you get the cross arrow as shown below. Now double click. This will fill the rest of the rows till the end is reached.

I didn’t mention the significance of the numbers earlier. Match() function is returning the relative position of the values based on comparision to first list. So here you can see “Johnson n Johnson” is in the 1st ordinal in our array. So is the case for others too.

The irritating factor here is the ERROR “#N/A”. The simple method is to prefix our function with the IFERROR() function. In our case, if we get n error we are going to print the text “Missing”.

Now use the cross arrow trick again. And now the final output looks like below.

So I plan to write the next two methods tomorrow. Want to guess them? If they are correct, I will approve the comments after making the posts :) … Do let me know if you want me to write on anything.

Continue reading...