Increment row number based on values in another field

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

Transformation

Preparation

Accepted answers

rohanonline

Another option - Use TILE tool with following configuration -

Tile Method: Unique values

Unique Fields: Tick Project SYS ID

All comments

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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703