Is there a way to incrementally count unique values per grouping?

For example,  I have the table below with Group and Category columns.  For each Group, I'd like to know assign a incrementing counter for every unique Category value.  Rows the the same Group and Category combination should have the same Counter value (ex. Group 1, Categ A rows have the same Counter value).   Thanks in advance.

I have an idea you might try.  First, I would add a unique identifier column that concatenates the Group and Category columns together.  So you'd have "1A", "1B", "2A" etc.  Then I'd add a Remove Duplicates step.  With the Unique ID column as your key and Group as your Comparison.  Then add a Group step that is grouped by the "Group" column.  The grouping step should automatically give you a count column with the count of the unique values for each group.  This may not be ideal because it removes and consolidates your data but if you just need a count it may work for you.  Hope this helps.

Hi Charles,

I'm after an incrementing count... similar to the Row() function but per Group (not in relation to the entire table).  So let's say Group 1 has 4 unique Category values (A, B, C, D), the row with Category A will have a Counter value of 1, Category B has 2, C has 3, and D has 4.  The numbering would then restart with a new Group. 

Maybe you could use the Sum IF function https://tbmcouncil.jiveon.com/docs/DOC-4904? I'm not completely clear on what your expected results would be in your example - if you want each row with a category of 4 D to show 4, you would sum the Counter value; if you want each 4 D to 2 (since there are two rows) you would want each line to be counted once.

Hi Robert,

I considered Sumif(), but it will only give me the sum per Group.  I need counter to increment for every row of a given Group.

Looking into this, two possible ways of doing this;

1. Formula

I could not work out the formula to do this but intuitively I think it will require a concatenation as per @Chuck Krussel comment. I would add that I was trying to do this using the following formulas;

2. Using a group step and a series of tables

Table one; upload table that does not go anywhere just load the data

Table two; Upload step is using existing table, source Table one, group by the Group Categ concatenated column, formula to create the Counter

Table three; Upload step is using existing table, source Table one, lookup in to table two using the Group Categ concatenated column to bring in the Counter

Not sure I will have time to have a go at option two before Christmas but will update you if I do. Not an easy one.

Hi Paul,

Thanks for the reply.  The tricky part is the formula to determine the Counter value - I need it to restart from 1 every time there's a new Group (not continuous incremental counting from the top to the bottom of the whole table). =) 

Hey all,

Not sure how there came to be two articles for this one question, but I offered a potential solution on the similarly authored article over here (link) for those interested.

-Greg

Thanks for your corroboration Gregory!