Increment row number based on values in another field

[aehrenwo]

I have a data source that has project numbers and then user names associated with those projects. I want to set a number field starting with one for the first project and increment up to 2 when I get to the next Project number in the list. 

I am trying to do a multi-row formula that looks something like this but can't get it to work (UserColNum was populated with 1 in all rows to start)

IF [Project SYS ID] != [Row-1:Project SYS ID] THEN [UserColNum] + 1
ELSE [UserColNum] ENDIF

It is only counting up to 2 for the first row where the Project Sys ID is different but the next row stays at 1. How can I get the next rows to be 2 and then up to 3 on the next Project system ID number? 

I am going to eventually use this number to transpose the field so all of the users will end up as new columns across the top for each Project. 

Thank you for the help.

Adam

[Federica_FF]

HI @aehrenwo

I think you should lookup at the Row-1 UserColumn in order to set the right values:

if [Project SYS ID] != [Row-1:Project SYS ID] then [Row-1:UserColNum]+1  else [Row-1:UserColNum] endif

Or you can take a different way using a summarize group by [Project SYS ID] to get a list of unique ID values, add a RecordID tool after it and then joining back the flow to the input using as match field [Project SYS ID].

[rohanonline · Accepted solution]

Another option - Use TILE tool with following configuration -

Tile Method: Unique values

Unique Fields: Tick Project SYS ID

[aehrenwo]

Once I figured out the parameters. The Tile Tool worked perfectly. 

[Nicole_L]

Thanks the tile function works great to de-identify a customer ID/Patient ID.