Count and Occurrence of Duplicates in a Column

Geoff Hill

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.

Thanks for your help!

Paul Newcome

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)

Paul Newcome

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))

Paul Newcome

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)

Geoff Hill

Thanks Paul! Worked perfectly! I appreciate your help!

Paul Newcome

Happy to help. 👍️

Geoff Hill

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]<>""

Paul Newcome

Exactly which formula are you trying to incorporate this into?

Geoff Hill

=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.

Paul Newcome

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))

Geoff Hill

Thank you kind sir! That worked perfectly!!

Paul Newcome

Happy to help. 👍️

CAS the CSA

Is there really no functionality to do anything like this without infinite helper columns?

Paul Newcome

@CAS the CSA this solution only required one column more than what was originally started with. It certainly doesn't require infinite helper columns.

CAS the CSA

https://community.smartsheet.com/discussion/comment/433102#Comment_433102

…everything in smartsheets requires helper columns, infinitely…

Paul Newcome

@CAS the CSA Not nearly everything requires helper columns. More advanced functionality does require some helper columns, but that is expected functionality in any similar program.

There are also solutions that reference helper columns that don't necessarily require them either. It just makes certain processes easier to manage.

Have you run into any use cases that required so many helper columns that it broke the sheet?

CAS the CSA

https://community.smartsheet.com/discussion/comment/433109#Comment_433109

That is an asininely high standard. Having to put helper columns in for nearly every basic thing one tries to do is excessive.

Paul Newcome

It isn't for every basic thing one tries to do. Basic things don't require helper columns. Sure… There is always room for improvement, but that is true on any platform. And like every other platform, more advanced actions require a little bit of help or creativity.

What is asinine is complaining about a system requiring you to add a few columns as if it should be programmed to accommodate exactly what you want as opposed to a more flexible system that accommodates a wide variety of use cases.

Are there any programs you have found that do exactly what you want without you having to do any work, or do you complain like this about everything?