Transpose but need to create duplicate rows

[tt2k]

Hi all,

I have a set of data, hundreds of lines for 1 week period. I need to easily change the original data to the way that it lists it in each row. Please find atatched excel sheet to understand what i mean.

So it needs to create duplicate of column1 5 times and then tranpose the rest of the other rows.

Thank you

[XLent]

using your sample file, the below will show one way of doing this using your sample data / ranges etc

Please Login or Register  to view this content.

the range references that pertain to columns A:F would need to be adjusted to reflect your real data (rows etc), whilst the references to likes of I3 etc should be updated to reflect wherever you choose to recreate your data.

you can probably achieve the same using a multi consolidation pivot table route but tbh the above is relatively lightweight to do with calcs... similarly it would be straightforward to code, if required.

[Pete_UK]

You can use the formulae below in the cells stated:

A12: =INDEX($A:$A,INT((ROWS($1:1)-1)/5)+3)

B12: =INDEX($B$2:$F$2,MOD(ROWS($1:1)-1,5)+1)

C12: =INDEX($B:$F,INT((ROWS($1:1)-1)/5)+3,MOD(ROWS($1:1)-1,5)+1)

Then copy down as far as you need to.

Hope this helps.

Pete

[Special-K]

in A12
=INDEX($A$2:$F$4,QUOTIENT((ROW()-12)+1,6)+1+1,1)
in B12
=INDEX($B$2:$F$2,1,MOD((ROW()-12),5)+1)
in C12
=INDEX($B$3:$F$4,QUOTIENT((ROW()-12)+1,6)+1,MOD((ROW()-12),5)+1)
and copy down the columns

This is based on your test data, expand the INDEX() range for your proper data.

[XLent]

edit: I just tested the multi consolidation pivot, and it would be pretty quick -- steps to do this:

on sheet holding data to be transposed...

1. hold ALT press D then P
2. select Multiple Consolidation Ranges
3. click Next
4. in Range dialog click Arrow and select your range (A2:F4), and click little arrow in box to return to main dialog
5. click Finish
6. double click on Grand Total (57)

you will now have your transposed dataset.

[tt2k]

edit: I just tested the multi consolidation pivot, and it would be pretty quick -- steps to do this:

on sheet holding data to be transposed...

1. hold ALT press D then P
2. select Multiple Consolidation Ranges
3. click Next
4. in Range dialog click Arrow and select your range (A2:F4), and click little arrow in box to return to main dialog
5. click Finish
6. double click on Grand Total (57)

you will now have your transposed dataset.

Hi XLent

This works great. Thanks very much, saves me a lot of time as this is a weekly thing i need to do with hundreds of lines