In this article we will learn how to find the duplicate items in combined columns. To find duplicate items we use the “COUNTIF” and “IF” formulae in Microsoft Excel.
IF:-IF condition allows us to specify multiple conditions in a cell. It helps us to create the flexibility of the function in Microsoft Excel . Upto a maximum of 64 conditions can be placed inside an IF function.
Syntax of “IF” function: =if (logical test, [value_if_true], [value_if_false])
COUNTIFS: This function is used to return the count of same entry in the multiple range..
Syntax of “COUNTIFS” function: =COUNTIFS (Criteria_Range1, Criteria1,Criteria_Range2,Range2)
Let’s take an example and understand how we can find duplicate items in combined columns in Microsoft Excel.
Example: – We have a list of names in range A2:A28, in which one name is entered many times. We need to find all the names which are entered more than once.
To do this, follow the below mentioned steps:-
- Select the Cell C2, and write the formula to find the duplicate entry.
- =IF(COUNTIFS($B$2:$B$28,B2,$A$2:$A$28,A2)>1,”Duplicate”,”Unique”) Press enter on the keyboard.
- The function will return the duplicates – for example there are 2 entries for Aaron on 10th Feb.
- To Copy the formula in all cells press the key “CTRL + C” and select the cell C3 to C28 and press key “CTRL + V” on your keyboard.
To convert the formula into value use the “Paste Special” option:-
- Select the range C2:C28, Copy by pressing the key “CTRL + C”
- Right click on the mouse select “Paste Special”
- In the dialog box select values and click on ok
- The formula will be replaced with values
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org