Count and Occurrence of Duplicates in a Column
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!
Best Answers
-
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))
Answers
-
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!
-
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!!
-
Is there really no functionality to do anything like this without infinite helper columns?
-
@CAS the CSA this solution only required one column more than what was originally started with. It certainly doesn't require infinite helper columns.
-
…everything in smartsheets requires helper columns, infinitely…
-
@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?
-
That is an asininely high standard. Having to put helper columns in for nearly every basic thing one tries to do is excessive.
-
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?
Help Article Resources
Categories
Check out the Formula Handbook template!
