Saturday, June 10, 2017

How to compare two lists of values in Microsoft Excel - Java

If you are working in real world application then you may frequently face scenarios where you have to compare data e.g. you get two lists of values and you want to know which values are common between two lists, which values only exists in the first list and which values only exists in the second list. If the list contains just 5 to 10 values you can do it easily with your eyes but what is fun if a programmer doing the task manually. You should be able to leverage all the tools available to you to do this comparison reliably. One of such ubiquitous and omnipresent tool is Microsoft Excel which is much more powerful then you can think of. It's like your brain, most of us just use 5% of Excel and a good knowledge of Excel's little bit advanced functionality e.g. VLOOKUP can be very helpful while comparing the list of values and reconciling data between different worksheet.

This is the second article about my series Essential Microsoft Excel tips for Java developers. In the first article, I have shown you how you can enclose a list of values into single quotes and separate them with a comma so that you can use them on IN clause of your SQL queries.

In this article, I am going to share one of the easiest but powerful technique to compare two lists of values in Microsoft Excel.

We'll use "Conditional Formatting" feature of Microsoft Excel to highlight both unique and duplicate values from two lists of values. This will help you to find common values between two list and values which are only present in individual lists. This is also the quickest and easiest way to reconcile two lists of values as you don't need to use the VLOOKUP function, which many developers would insist.



How to find duplicate and unique values in two lists using Excel

Here are the exact steps to compare two lists of values in Microsoft Excel:

1) Paste two lists of values into adjacent columns in Excel

How to compare two lists of values in Microsoft Excel - Java




2) Select these two lists of values

How to find duplicate values in two list in Microsoft Excel


3) Go to Conditional Formatting, Go to Highlight Cell values and Select Duplicate values rule

How to compare two list of values in Excel



Depending on which color formatting you see, you will see the common values or duplicate values highlighted in red and unique values e.g. strings which are present in only one list is highlighted in green. This way you can easily find out which items are missing in the first or second list, or which items are common between two lists.

How to find values only exists in first list using Excel

The rule also gives you the option to select either duplicate or unique values as well as some other color highlighting options for your preference. The only thing you need to remember is that this trick will only work from Microsoft Excel 2007 version, which is anyway quite old given we are now on 2017.



That's all about this nice little Excel tip for Java developers to compare two lists of values in Microsoft Excel. If your job involves data analysis or support then you should spend some time to learn Microsoft Excel.

One of the best course to learn Microsoft Excel is the Excel 2013 Fundamental course by Heather Ackmann, which you take on PluralSight. I strongly recommend every programmer to attend this course to get themselves familiar with the power of Excel. You will appreciate your decision of learning Excel more than an average programmer, whenever you face such situation to analyze data and find out what you want.

Further learning
Microsoft Excel Fundamentals 
Microsoft Excel 2013 Data Analysis and Business Modeling
Excel 2016 Bible by John Walkenbach
How to read XLS file in Java using Apache POI
How to modify Excel file in Java

Thanks for reading this article so far. If you like this tip then please share with your friends and colleagues. If you have any question or if you would like to some of the useful Microsoft Excel tips you are using with us then please drop a comment. 

No comments :

Post a Comment