On excel, comparing values within the same column is simple, but this is not the case when comparing multiple columns.
Multiple columns can be compared on excel via sorting, conditional formatting, and filtering. However, these options are applied based on conditions. But these options may not work at all times. In this tutorial, we will guide you on how to compare four cells using a simple formula in excel.
Steps to compare four column
1. Considering the table below, one column contains dates, while the other column is for initials. There are some repetitions, and it is easy to spot them. But, what if you were working on a large data set?
We will carry out the comparison via the concatenate function.
2. Type the formula below in D3 and copy to the range, D4: D10.
=B3&C3
This is a very flexible technique, and you can apply the concatenate option on many columns. The serial values will be returned by excel but will not have any interference with the technique.
3. Go ahead and count the concatenate results in column D by entering the formula below in E3. Go ahead and copy the formula into the remaining cells.
=IF(COUNTIF($B$3:B3, B3)>1, "Duplicate!", "Original")
It is apparent that this technique is very simple, just like sorting. You can determine the duplicate data, and you do so by understanding the right column to concatenate.
4. The COUNTIF() function will count the concatenate and display the frequency of its occurrence in the range.
When it is more than one – The formula will display, "Here I am! I'm a duplicate!", else, the formula will display the string, "Original."
You can include extra columns when you use this technique. Check the concatenate formula and add each new. Column to it. As I mentioned earlier, there are other ways of comparing columns on excel, but this method is quick and simple, and it does not require any specialized knowledge.