Replicate Excel COUNTIF function with incrementing count in new column

[NikiJF]

Hi, I've tried searching through past answered questions but have not been able to find a solution, so I apologise if this has already been solved.

I am trying to replicate an incrementing CountIF function in Excel. I have a set of 6500+ records (sample attached). There are generally 10 or 12 records per Client Ref 4 (sometimes less). Currently in Excel I identify the record number per Client Ref 4 using the COUNTIF function which is copied down to all populated rows, and then I use a VLookup to bring through a corresponding code for subsequent action.

In Alteryx I need to create a new column with the same incrementing record number. Further in my workflow I will need to identify those that have less than 12 records as they need to be treated differently. I hope I have explained this in a way that makes sense 🙂

Thanks in advance for any help!

[DavidP]

Hi @NikiJF 

The Summarize tool has a Group by function. You can use this to create a group for every Client Ref 4. You can then either count the number of records in each group, or, since you have Condition No in your data, you bring through the max for this. There are many more things you can do within each group in the Summarize tool, which you should explore.

[NikiJF]

Hi @DavidP, thanks very much for having a look. The Condition No is the value that I need Alteryx to calculate for me - that's what I'm currently using Excel for (with the COUNTIF function), but I want Alteryx to populate that column for me, ie if there are 12 records for Client Ref 4, I want it to put 1 against the 1st one, 2 against the 2nd, etc up to 12, so I can then use a join function to pull through the Condition. Is this something that can be done in Alteryx?

[NikiJF · Accepted solution]

Hi again @DavidP - I've discovered the Tile tool which has solved this for me 😃 Thanks again!

[DavidP]

Hi @NikiJF 

The tile tool is one of my favourites! I didn't realise that you wanted all the Condition numbers inbetween. The Tile tool is indeed the best tool for that job.