Archive for July 19th, 2013

Excel Tip: Compare two lists (Method 3) July 19th, 2013

Vinod Kumar

This is in continuation to our series where we want to compare two lists and find the difference. In previous posts we discussed the use of MATCH() and then VLOOKUP() functions. In this post, I will use the other popular method of PIVOT TABLES. Just to reiterate, we have two lists “List A” and “List B” and we want to know how many values in List B is not available in “List A”.

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function.

Method 3: Using PIVOT TABLE.

For PIVOT Table, I am going to prepare the above two lists into one mega list. I have added a column to denote Year and merged both these lists like below.

Now that our data is prepared, our next step is to initialize the PivotTable. Goto INSERT Tabe –> PivotTable.

The Create PivotTable dialog comes up. Under Select a table or Range, select our complete table. Under PivotTable Placement-> Select a cell in our same worksheet. I have shown a typical example below.

To aggregate Drag the corresponding columns to get the result I have shown.

Columns –> Year

Rows –> Company

Values –> Revenues

Voila, our task is complete. Now in one shot we have got, the red empty boxes are the new values in 2012 that are not available in 2011. While the blue box below represents, values in 2011 that are missing in 2012 year.

Thanks for reading this far. I hope this was useful and you learnt something new. Do let me know if you want me to write some other topic.

Continue reading...