add a unique number +1 after a certain number of cells

[bl4h]

Hi all,

Would like to know if its possible to add a unique number like the one i added in "ref no" tab column.
Criteria is to add +1 so that i can identify the groups of data by the "ref no" column.

After adding the "ref no" i would like to eliminate those empty row to have a cleaner excel sheet.

Can refer to the attached for a sample i created. I would like to know any possible method to automate it since i have huge amount of data. 

[Jotigautam]

@bl4h It is possible to add column. Could you please tell what is the criteria to put ref no., if it is based on value in some other column ? I assume it is not based on date. It would be great if you can attach some sample data. 

[binuacs]

@bl4h one way of doing this

[bl4h]

Hi. Sorry for the confusion. I was wondering if its possible to use Alteryx to create a column similar to those in "Ref No" column. Whereby the first set of data is numbered as "0" then "1" in the next set after the empty row. Incremental +1 after every blank row. Without going thru the entire data sheets of 1000+ row to add manually. 

[DavidP]

Hi @bl4h 

The Tile tool is very handy for doing this, but 1st, remove the null rows with a Data Cleanse tool. The Tile tool starts counting at 1, so you have to fix that with a formula tool if you want to count from 0.

[binuacs]

@DavidP The tricky part of this use case is the user want to have unique ids after each blank rows even though the date are same. If you take a look at the dates 7-Jan-22 the id are 1 and 2. 

[binuacs · Accepted solution]

@bl4h updated the workflow with the given input data