Convert Rows with Multiple Columns to Rows - Excel 2001

[CarolCook]

Hi:
I have a spreadsheet that has multiple rows with multiple columns. I need to create a text output that has a row for each column:

From:

Col 1 Col 2 Col 3

Row 1 Row1,Col1 Row1,Col2 Row3,Col3
Row 2 Row2,Col1 Row2,Col2 Row3,Col3

To:

Row1,Col1
Row1,Col2
Row1,Col3
Row2,Col1
Row2,Col2
Row2,Col3

This is an oversimplication of course but you get the idea. This is not a Pivot Table, it is just a spreadsheet. These are all string values. The actual data has 34 rows and 10 columns.

Thanks

 

[bluedragon2]

Quick way:

make a helper column with numbers 0 through 9 repeated down, then in another column put:

=OFFSET($A$1,INT(ROW(K2)/10-0.1),L1)

and copy down. L1 is the helper column, change to wherever you have it.

If this is an ongoing thing, you can replace the helper column with a formula to extract that information as well, I just don't have the time to play with it.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

formula should be =OFFSET($A$1,INT(ROW(A1)/10-0.1),L1)

Sorry

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[CarolCook]

Thanks for the response. I tried a pivot table report and that gave me most of what I needed but I am happy to know about this.
Thanks!

 

[SkipVought]

faq68-5287


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Welshbird]

I can vote for Skips FAQ - it saves me HOURS!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[GlennUK]

I also can vote for Skips FAQ ... I have memorised it now, after having used it many many times.

Cheers, Glenn.

Beauty is in the eye of the beerholder.