Identify sequential numbers across different rows

[Data_Alter]

I have this data where I want to identify all the sequential numbers across different rows and give them a unique reference

For eg. the few lines from the data I have

ID	Amount
2515669	429
2515670	86
2515671	-515
2515894	170
2515895	34
2515896	-204
2515908	80
2515909	16
2515910	-96
2515916	150
2515917	32
2515918	-192
2515919	5
2515920	5
2515146	-5
2515147	5

Desired Result

ID	Amount	Unique Reference
2515669	429	1
2515670	86	1
2515671	-515	1
2515894	170	2
2515895	34	2
2515896	-204	2
2515908	80	3
2515909	16	3
2515910	-96	3
2515916	150	4
2515917	32	4
2515918	-192	4
2515919	5	4
2515920	5	4
2515146	-5	5
2515147	5	5

Please can someone help !!

[Emil_Kos]

Hi,

In order to achieve it you need to use multi row formula tool.

I have prepared a workflow for you:

The formula is:

IIF([Row-1:ID]+1=[ID],[Row-1:New Field] , [Row-1:New Field]+1)

The output:

Please mark my post as a solution if this is helpful for you!

[Data_Alter]

Thanks @Emil_Kos 

That is what I looking to do but it doesn't give me the result I wanted for the full data I have.

Is it possible to do this on the Amount column ? For e.g a unique reference for the rows until when the Amount column nets to zero.

For e.g in the data below first three rows net to zero , then next eleven net to zero and so on.

Amount
-253
-51
304
480
-2880
0
-2420
0
-29
2400
1210
1210
20
9
4
1
-5
362
72
-434

Desired result 

Amount	Unique reference
-253	1
-51	1
304	1
480	2
-2880	2
0	2
-2420	2
0	2
-29	2
2400	2
1210	2
1210	2
20	2
9	2
4	3
1	3
-5	3
362	4
72	4
-434	4

[Emil_Kos]

Hi @Data_Alter,

Ruining total is very helpful here.

With combination of mutlirow formula tool I could achieve the result.

There is one small difference with the output as the last position should be in the 5th group. At least that is my understanding. 

[Data_Alter]

Thanks @Emil_Kos 

That is brilliant. It is very close.

The unique reference changes from the row where "RunTot_Amount" becomes 0. Whereas I want it be changed after that .

Please see the "result I want" colum. IS there a quick way to fix this ?

Amount	RunTot_Amount	New Field	Result I want
-253	-253	1	1
-51	-304	1	1
304	0	2	1
480	480	2	2
-2880	-2400	2	2
0	-2400	2	2
-2420	-4820	2	2
0	-4820	2	2
-29	-4849	2	2
2400	-2449	2	2
1210	-1239	2	2
1210	-29	2	2
20	-9	2	2
9	0	3	2
4	4	3	3
1	5	3	3
-5	0	4	3
362	362	4	4
72	434	4	4
-434	0	5	4

[Emil_Kos · Accepted solution]

Hi,

I am blind that I have missed it.

I just changed the point of reference. Now I am looking for the net amount equals to zero in the previous line instead of the current line. 

[Data_Alter]

Thanks @Emil_Kos for your help.