- Concatenate the columns you're checking.
- Use CountIf() to count the number of combined values.
- In cell C2 enter the formula =A2&B2. (You can combine more columns.)
- Copy the formula to C3:C9. Excel uses each date's serial value, but that won't interfere with the technique. However, if your values contain times, it might, depending on how the time values were entered.
- In cell D2 enter the following formula: =IF(COUNTIF($C$2:C2,C2)>1, "Here I am! I'm a duplicate!","Original")
- Copy the formula to D3:D9. At this point, finding multi-column duplicates is as easy as sorting by column D (although this example doesn't require any sorting).
The IfCount() function counts the number of times the concatenated values occur within the extending range. If the count is greater than 1, the formula returns the string "Here I am! I'm a duplicate!"; when the count isn't greater than 1, the formula returns the string "Original." Only the first occurrence will be identified as Original. This technique easily adapts to include additional columns. Simply add each column to the concatenating formula (column C in this example). Of course, there are other ways to identify multi-column duplicates in Excel. If you'd like to share a more efficient solution or a more complex problem, please start a conversation below.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.