Sequentially number records based on Rep & Number of weeks

[mmbg48]

I am trying to number from 0-x for each sales rep that have different number of records based on week ending date. For instance rep 1 started 1-1-2016 and my data goes to 12/31/2018 so they would have roughly 156 weeks(records). Rep 2 started in 1-1-2018 and again data goes to 12/31/2018 so they would have 52 weeks. So I want to number each week ending date accordingly. Thanks for any help in advance. I have attached some sample data with the column week number being what I want to sequentially number.

[mmbg48]

I tried using that earlier but it was numbering the whole file not by rep.

[Claje · Accepted solution]

Hi,

Try the Multi-Row Formula tool with the following code:

(([Row-1:Week Number])+1)

And use the "Group By" Settings inside the tool to Group By your "Consolidated Rep" field.

You will then need a formula tool afterwards to get these numbers to start at 0 for [Week Number] 

[Week Number] -1

EDIT:

Alternatively, if you set the "Values for Rows that don't exist" to "NULL", you can use this Multi-Row Formula

IF ISNULL([Row-1:WeekNumber]) THEN
0
ELSE
[Row-1:WeekNumber]+1
ENDIF

This is a slightly more complex formula, but saves you a tool.

[mmbg48]

Thank you for that I do not think I was grouping by rep sorry I missed that in your reply through my email.

[Claje]

No problem!

I was editing in extra stuff after I posted (like the screenshot above) so I'm not surprised that it might have been missed.

[mmbg48]

Thanks again, worked perfectly with the advanced solution!