Combine Rows under existing recored Id

[hellyars]

Hi,

A record ID changed.  Year 1 depicts the sales using the old ID.  Subsequent years show sales using the new ID.

I want to move the sales for Year 1 to the new ID.  Sometimes the switch occurs after Year 2How can I do that?

Any ideas?  I assume a Multi Row Formula tool, but I am a bit stuck.  

	ID	Year1	Year2	Year3	Year4
A--Old ID	0604120	34	0	0	0
A--New Id	1206120	0	42	41	46
A-Desired	1206120	34	42	41	46
B--Old Id	0303142	123	166	0	0
B--New ID	1203142	0	0	175	225
B-Desired	1203142	123	166	175	225

[CharlieS · Accepted solution]

I assume you have some sort of look up table or a way to link new and old IDs? If so, use that to assign the same ID to all records of the same customer. If you have a look up table, the Find Replace tool will be handy. 

Once you have a common ID system assigned, a Summarize tool can be used to group by that common ID and find the sum of each year field. I have attached an example workflow.

[hellyars]

I have not come across that tool. But, it might be my new best friend.  Thank you .