Complex Transposition

[Leo Demarce]

I searched the forum for this but couldn't find what I was looking for. If
the solution already exists then please point me in the right direction.

The issue is that I have a table that looks like this:

Period Account1 Account2
1 100 125
2 80 130

What I need to figure out is how i can transpose the data to look like this:

Account Period Amt
Account1 1 100
Account1 2 80
Account2 1 125
Account2 2 130

Of course the table is quite large (12 periods by 350 accounts)
Is there any suggestions?
Thanks in advance
Leo

 

[Luke M]

How does your original table have more than 256 columns? I'll assume your
placing start of new table into A20

Basing formulas off of your statement of 12 periods.
First column (Account names)
=OFFSET($B$1,0,INT((ROW(A1)-1)/12))
Second column (Period)
=ROW(A1)-FLOOR(ROW(A1)-1,12)
Third column (your data)
=INDEX(B2:IV13,MATCH(B21,A2:A13),MATCH(A21,B1:IV1))

You may need to adjust range references in that last formula, because I'm
not sure how wide your original table really is.

 

[Leo Demarce]

Thanks Luke,

Here is more info to elaborate:

Columns of data run from A to MC which is 341 columns (using excel 2007).
Rows of data run from 1 to 14. row 1 contains the Acct#, row 2 is Opening
Balance, row 3 to 14 is period 1 to 12.

Transposition for account 1 and 2 would be
Acct Period Amt
account1 open 100
account1 1 50
account1 2 75

etc.

Does this clarify?
And again, thank you for your assistance. I will work on your 1st answer in
the meantime.
Cheers

 

[Luke M]

Thanks for clarifying. Adjusted formulas, redid 2nd column to account for text.
1st column:
=OFFSET($B$1,0,INT((ROW(A1)-1)/13))
2nd column:
=IF(A21<>A20,"open",ROW(A1)-1-FLOOR(ROW(A1)-1,13))
3rd column:
=INDEX(B2:MC14,MATCH(B21,A2:A14,0),MATCH(A21,B1:MC1,0))

Again, the assuming beginning of new table is A20 (first line of 'data' is 21)

 

[Leo Demarce]

Yes, that worked. I played with the original formulas and made adjustments
and that worked out great!

Thanks again!
Leo