Consolidating/Transposing Multiple Column Data into Common Data Columns

[sjkl_99]

Hi all,

Hoping someone can assist with some data column to row transposing that I'm trying to do in Excel. It's not the easiest to describe so best to take the example data below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Person
[/TD]
[TD]Acc No
[/TD]
[TD]Currency
[/TD]
[TD]Acc No
[/TD]
[TD]Currency
[/TD]
[TD]Acc No
[/TD]
[TD]Currency
[/TD]
[TD]Acc No
[/TD]
[TD]Currency
[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]000111222[/TD]
[TD]USD[/TD]
[TD]00012345[/TD]
[TD]AUD[/TD]
[TD]000143243[/TD]
[TD]GBP[/TD]
[TD]000234323[/TD]
[TD]EUR[/TD]
[/TR]
[TR]
[TD]987654[/TD]
[TD]000888999[/TD]
[TD]AUD[/TD]
[TD]00123423[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

"Person" is the key value, for which you can have multiple "Acc No" and "Currency" combinations as a series of columns. What I want to do is to transpose the Acc No and Currency values into a 3 column output as below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Person
[/TD]
[TD]Acc No
[/TD]
[TD]Currency
[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]000111222[/TD]
[TD]USD[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]00012345[/TD]
[TD]AUD[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]000143243[/TD]
[TD]GBP[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]000234323[/TD]
[TD]EUR[/TD]
[/TR]
[TR]
[TD]987654[/TD]
[TD]000888999[/TD]
[TD]AUD[/TD]
[/TR]
[TR]
[TD]987654[/TD]
[TD]00123423[/TD]
[TD]NZD[/TD]
[/TR]
</tbody>[/TABLE]

There are dozens of columns that repeat as per the input layout above, so i'm hoping the general principle of a solution can be extended across X no of columns (without too much manual intervention).

Any solutions very welcomed - Thank you!