replacing the repeating value with zero

[Neffri]

Hi All,

can you tell me how to convert repeated values for the same ID to zero?

I thought maybe the Multi-Row formula, but then do I need to create to each column separate calculation?

Customer Number	Current Amount	Amount Due	Aging Amount 1-30	Aging Amount 31-60	Aging Amount 61-90	Disputes Amount
1	1200	300	100	150	50	16
1	1200	300	100	150	50	40
1	1200	300	100	150	50	50
2	40000	500	0	300	200	17
2	40000	500	0	300	200	80
2	40000	500	0	300	200	90
2	40000	500	0	300	200	135
2	40000	500	0	300	200	172
2	40000	500	0	300	200	208
2	40000	500	0	300	200	245
3	5000	150	70	30	50	78
3	5000	150	70	30	50	43
3	5000	150	70	30	50	8
3	5000	150	70	30	50	67

The result which I want to achieve is in the attachment.

Thank you for help.

[binuacs · Accepted solution]

@Neffri one way of doing this with the transpose tool

[Qiu · Accepted solution]

@Neffri 
I use the similar approach with @binuacs but adding some logic to return the processed data to its orginal field name and order.
So it can be dynamic and still work even the columns are increasing or decreasing.

[flying008 · Accepted solution]

Hi, @Neffri 

Another way like @Qiu .

[Neffri]

@Qiu Thank you for your solution I have much more column so I hope it will work :)

[Neffri]

@Qiu Can I ask you about the opinion if I can use the workflow for such data?

I tried to adjust the workflow but I have some problem.

[Qiu · Accepted solution]

@Neffri 
The sample you gave at first only contains one non-numeric column, which is not the case for 2nd sample data.
To be more dynamic, I use Auto Field and Dynamic Select Tool to separate the Numeric and Non-Numeric Columns, then join back together after wards.
One thing is that the column order is now changed.

[Neffri]

@Qiu Thank you so much <3