Tuesday, 4 July 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.

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

Oracle Java Tutorials and Materials, Oracle Java Certifications

2) Select these two lists of values

Oracle Java Tutorials and Materials, Oracle Java Certifications

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

Oracle Java Tutorials and Materials, Oracle Java Certifications

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.

Oracle Java Tutorials and Materials, Oracle Java Certifications

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.