Converting Columns to Rows using a Loop

[DHazen]

I have a table with 25 columns and 2000+ records. Columns 2 through 25 are different values for the first column. I need to keep column 1 as column 1, but make columns 2-25 as column 2 Row 1, Column 2 Row 2, Column 2 Row 3, etc. and then start with the next record once Column 26 is reached. I may have blank cells in the columns so I cannot stop the loop when the field is blank. I have attached a sample of what I am trying to accomplish and am hoping that someone can understand what I wrote.

Thank you for your help!

Original Data

Clothing Color1 Color2 Color3 Color4 Color5
Shirt Red Yellow Blue Green Orange
Pants Yellow Blue Green Orange Red
Hat Blue Green Orange Red Yellow
Scarf Green Orange Red Yellow Blue


I need it to look like this:

Clothing Color
Shirt Red
Shirt Yellow
Shirt Blue
Shirt Green
Shirt Orange
Pants Yellow
Pants Blue
Pants Green
Pants Orange
Pants Red

[martindwilson]

they seek it here they seek it there but cannot find attachment anywhere !

[Paul]

Hi DHazen, welcome to the forum.

You don't necessarily need programming for this task, although it may improve the processing time, and be less volatile than the solution I'm about to show you...

If your original data is on Sheet1 in cells A1:Y2000 (25 columns by 2000 rows), then on Sheet2 put the headers Clothing and Color in A1 and B1.

In A2 put the formula: =OFFSET(Sheet1!$A$2,FLOOR((ROW()-2)/24,1),0)
In B2 put the formula: =OFFSET(Sheet1!$B$2,FLOOR((ROW()-2)/24,1),MOD(ROW()-2,24))

Fill those formulas down to about row 48,000 (2000 clothing records * 24 colors per clothing). There's your new table. If you don't need the formulas any longer, you can select columns A and B on the new sheet and click Edit -> Copy, then click Edit -> PasteSpecial -> Values -> OK.

If you truly want a macro approach let us know.

[DonkeyOte]

Another alternative might be to use a Multi Consolidation Pivot Table with source set to original table with 0 page fields, see attached based on sample file... by drilling into the Grand Total on the PT you can see the data transposed (you can remove column B from the detail generated sheet).

Paul, you could switch the OFFSET to INDEX based thereby avoiding Volatility issue ?

[DHazen]

Paul,

That worked perfectly!

Thank you!

Deb

[johnpaul007]

Hi DHazen, welcome to the forum.

You don't necessarily need programming for this task, although it may improve the processing time, and be less volatile than the solution I'm about to show you...

If your original data is on Sheet1 in cells A1:Y2000 (25 columns by 2000 rows), then on Sheet2 put the headers Clothing and Color in A1 and B1.

In A2 put the formula: =OFFSET(Sheet1!$A$2,FLOOR((ROW()-2)/24,1),0)
In B2 put the formula: =OFFSET(Sheet1!$B$2,FLOOR((ROW()-2)/24,1),MOD(ROW()-2,24))

Fill those formulas down to about row 48,000 (2000 clothing records * 24 colors per clothing). There's your new table. If you don't need the formulas any longer, you can select columns A and B on the new sheet and click Edit -> Copy, then click Edit -> PasteSpecial -> Values -> OK.

If you truly want a macro approach let us know.

Thank you guys for the info. By this method could you tell me about the formula for converting columns to rows. Meaning the output of OPs Data is my input and needs to convert it to the format of OPs Input format.

Thank you for your time

[AB33]

John,
You need to start your own thread as per forum's rule.

[johnpaul007]

Oops my bad. Anyway I got the answer from the following link. Thanks

Ahttp://www.excelguru.ca/forums/showthread.php?2201-Help-wuth-transpose-(perhaps-)