To find the duplicate items in combined columns we use the “COUNTIF”, and “IF”formulae in Microsoft Excel 2010/2013.
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 2010 & Microsoft Excel 2013.. 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 2010 & 2013.
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.