Concatenate Multiple Variable Fields

[AndrewKwast]

I have a group of accounts, and for each account, I need to concatenate three sets of numbers a variable number of times. Using the sample tables shown below, I need to combine each "Code", "Digit", and "Number" field for each account, into one "Code" field. There could be hundreds of "Codes" for any given account. In this sample, there is one account with one code, one account with six codes, and a bunch of other accounts with other code amounts.

How do I concatenate every three columns together and ensure that, if there were even more columns, any of these extra set of three numbers would be picked up in the next data input (e.g. if next account input had hundreds of codes)?

Sample Before:

Account	Code 1	Digit 1	Number 1	Code 2	Digit 2	Number 2	Code 3	Digit 3	Number 3	Code 4	Digit 4	Number 4	Code 5	Digit 5	Number 5	Code 6	Digit 6	Number 6
71509021	30	715	00598	50	715	06743	80	715	09020	30	715	09021	40	530	66412
71509020	50	715	00598	30	715	09020
50028674	90	715	06743	30	715	00598	40	715	09020	30	715	09021	30	530	66412	40	715	06743
53066412	30	715	00598	40	715	06743	30	715	09021	30	715	09020	30	715	09022
53066413	30	715	00598	30	715	06743	30	715	09021
53083436	30	715	00598	10	715	06743	20	715	09021	30	715	09020	20	715	09022
53083437	60	715	00598	30	715	06743	30	715	09021
53083438	30	715	00598
71509022	30	715	00598	40	715	06743	30	715	09021	10	715	09020	30	715	09022
71500598	70	715	06743	30	715	00598
71506743	30	715	00598	50	715	09020	60	715	06743	30	715	09021

Sample Desired Result:

Account	Code 1	Code 2	Code 3	Code 4	Code 5	Code 6
71509021	3071500598	5071506743	8071509020	3071509021	4053066412
71509020	5071500598	3071509020
50028674	9071506743	3071500598	4071509020	3071509021	3053066412	4071506743
53066412	3071500598	4071506743	3071509021	3071509020	3071509022
53066413	3071500598	3071506743	3071509021
53083436	3071500598	1071506743	2071509021	3071509020	2071509022
53083437	6071500598	3071506743	3071509021
53083438	3071500598
71509022	3071500598	4071506743	3071509021	1071509020	3071509022
71500598	7071506743	3071500598
71506743	3071500598	5071509020	6071506743	3071509021

The only solution I've thought of could be to have a template with a certain number of codes (e.g. 1000 codes) and just use the formula tool to create hundreds of formulas saying combine 1,2,3, then combine 4,5,6, then combine 7,8,9, etc. Is there an easier way to do this?

[patrick_digan · Accepted solution]

@AndrewKwast I would suggest transposing your data. I've attached my solution

[ponraj]

Here is the sample workflow for your case.  Hope this is helpful. 

WorkflowResults