Author: Oscar Cronquist Article last updated on January 20, 2012

Michael asks:

I need to identify the duplicates based on the Columns D:H and put in Column C a small “d” + a running number for the duplicates which are duplicated the same.
If no duplicates only to put in a “d0” for instance.


Array formula in cell B3:

=IF(COUNTIFS($D$3:$D$11, D3, $E$3:$E$11, E3, F$3:$F$11, F3, $G$3:$G$11, G3, $H$3:$H$11, H3)=1, "d0", IF(COUNTIFS($D$3:D3, D3, $E$3:E3, E3, $F$3:F3, F3, $G$3:G3, G3)=1, "d"&(SUM(1/COUNTIFS($D$3:D3, $D$3:D3, $E$3:E3, $E$3:E3, F$3:F3, $F$3:F3, $G$3:G3, $G$3:G3, $H$3:H3, $H$3:H3))-SUM(IF(COUNTIFS($D$3:$D$11, $D$3:D3, $E$3:$E$11, $E$3:E3, F$3:$F$11, $F$3:F3, $G$3:$G$11, $G$3:G3, $H$3:$H$11, $H$3:H3)=1, 1, 0))), INDEX($B$3:B3, MIN(IF(COUNTIFS(D3, $D$3:D3, E3, $E$3:E3, F3, $F$3:F3, G3, $G$3:G3, H3, $H$3:H3), MATCH(ROW($D$3:D3), ROW($D$3:D3)), "")))))

How to create an array formula

  1. Double click cell B3
  2. Copy/Paste above formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1. Select cell B3
  2. Copy (Ctrl + c)
  3. Select cell range B4:B11
  4. Paste (Ctrl + v)

Download excel 2007 *.xlsx file

Group duplicates.xlsx