If you are working in the 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 exist 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 the such ubiquitous and omnipresent tools is Microsoft Excel which is much more powerful than 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 worksheets.
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 techniques to compare two lists of values in Microsoft Excel.
We'll use the "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 lists 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.
1. Paste two lists of values into adjacent columns in Excel
2. Select these two lists of values
3. Go to Conditional Formatting, Go to Highlight Cell values and Select Duplicate values Rule
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 that are present in only one list are 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.
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 in 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 learning Microsoft Excel. And, if you want to learn more about Excel, I strongly recommend every programmer to attend join these best Microsoft Excel courses to get themselves familiar with the power of Excel.
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 techniques to compare two lists of values in Microsoft Excel.
We'll use the "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 lists 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
2. Select these two lists of values
3. Go to Conditional Formatting, Go to Highlight Cell values and Select Duplicate values Rule
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 that are present in only one list are 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.
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 in 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 learning Microsoft Excel. And, if you want to learn more about Excel, I strongly recommend every programmer to attend join these best Microsoft Excel courses 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 a situation to analyze data and find out what you want.
Further learning
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.
Further learning
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