or Explore Discussions

# Count and Occurrence of Duplicates in a Column

04/22/21
Accepted

I'm trying to count the number of duplicates in a column and then number those duplicates in another column.

For example:

I'm using the formula COUNTIF([Legacy ID]:[Legacy ID], [Legacy ID]@row) in the Multiples column to count the number of duplicates in the Legacy ID column. I need help with a formula to create the MCount column.

• To set this up so that we can use strictly column formulas, we will need to insert two more columns. The first is an auto-number column that doesn't require any special formatting. The second is a text/number column with the following column formula:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

I'll call this text/number column "Row Number" for the sake of this example since it will duplicate the row numbers into something we can leverage, but you can call them whatever you want.

Now we can move onto putting together a formula that will allow you to remove the Multiple column all together which will save a little space since we added two columns.

So we already have the second number in the "# of #" string with your COUNTIFS:

=COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row)

To get the first number, we will leverage that [Row Number] column to count any rows that have a row number less than or equal to the current row number.

=COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row)

Now we piece it together like this...

=first_#_formula + " of " + second_#_formula

=COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) + " of " + COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row)

• Try this...

=IF([Legacy ID]@row <> "", COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) + " of " + COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row))

• To set this up so that we can use strictly column formulas, we will need to insert two more columns. The first is an auto-number column that doesn't require any special formatting. The second is a text/number column with the following column formula:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

I'll call this text/number column "Row Number" for the sake of this example since it will duplicate the row numbers into something we can leverage, but you can call them whatever you want.

Now we can move onto putting together a formula that will allow you to remove the Multiple column all together which will save a little space since we added two columns.

So we already have the second number in the "# of #" string with your COUNTIFS:

=COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row)

To get the first number, we will leverage that [Row Number] column to count any rows that have a row number less than or equal to the current row number.

=COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row)

Now we piece it together like this...

=first_#_formula + " of " + second_#_formula

=COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) + " of " + COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row)

• Thanks Paul! Worked perfectly! I appreciate your help!

• Happy to help. 👍️

• Hey Paul, can you help me figure out a way to not count if the Legacy ID cell is blank? I've tried NOT ISBLANK and [Legacy ID]<>""

• Exactly which formula are you trying to incorporate this into?

• =COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) + " of " + COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row)

Ideally, if the Legacy ID cell is blank, then this cell will also be blank.

• Try this...

=IF([Legacy ID]@row <> "", COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) + " of " + COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row))

• Thank you kind sir! That worked perfectly!!

• Happy to help. 👍️