Help with Converting Multiple Rows to Columns and add Column Headers

**Lmsloman** · 10-17-2009, 06:01 PM

Good Afternoon Everyone,

I'm currently faced with a spreadsheet that has data formatted like this:
A
1 RandomRowofData1
2 RandomRowofData2
3 RandomRowofData3
4 RandomRowofData4
5 RandomRowofData5
6 RandomRowofData6
7 RandomRowofData7
8 RandomRowofData8
9 RandomRowofData9

Every 9 rows, a new "set" of data repeats itself (wow, this is so hard to put into words)....

I need to figure out a way to get the data in column "A", every 9 rows, to transpose itself into 9 separate columns.

Make sense? I tried doing an OFFSET formula that I found on the support.microsoft website that "sorta" worked...but the formula was written for 4 rows of data and I'm having trouble personlizing it to suit my needs. Ideally, I'd like to add column headers from C1:K1 and paste the formula from cell C2 to K2 and down.

The formula in question is:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

Go easy on me...I'm pretty much self taught with my excel knowledge and the way I usually learn complex formulas is by applying them to my needs and dissecting them.

...sorry this post is so long, but any help is GREATLY appreciated!!

Thanks,

Laura (complex formula noob)

P.S. I have attached an example of the scenario I'm dealing with. Sheet1 shows the "before" and Sheet2 shows what I want my "after" to look like.

**JBeaucaire** · 10-17-2009, 09:16 PM

Put this in C2 and copy it down and across:

=OFFSET('This is my Data'!$A$1, ((ROW()-1)*9) - 9 + MOD(COLUMN()-3, 9), 0)

**Lmsloman** · 10-17-2009, 09:22 PM

Thank you so much! I love this message board!

**JBeaucaire** · 10-17-2009, 10:01 PM

I just realized the MOD() wasn't doing anything. Here's a condensed version of the formula:

=OFFSET('This is my Data'!$A$1,((ROW()-1)*9)+COLUMN()-12,0)

**Lmsloman** · 10-18-2009, 10:08 AM

Thanks so much!

I got it to work earlier by tweaking it a bit and used:

=OFFSET(Data!$A$1,(ROW()-2)*9+INT((COLUMN()-1)),MOD(COLUMN()-3,1))

It worked like a charm, I even set up a "tool" workbook so my novice user can easily import data into sheet2 and not alter any of the formulas set up on sheet1.

Sometimes you just need a second pair of eyes

**bala06** · 05-27-2010, 07:05 AM

Hi all

I would like to convert huge data of rows into columns.

I know paste special and transpose work but i'm having huge data.

Its difficult to do manually.

Could anyone help me on this please.