asked on

Rows To Columns...

I have repeating data in columns A and B that goes 6 rows before the data fields repeat again. THe data looks like this:

NUMBER      1
PART NUMBER      F5D8053
MANUFACTURER      BELKIN
DESCRIPTION      ADAPTER,N WIRELESS USB,G
UOM      EACH
QTY      10
NUMBER      2
PART NUMBER      MN2400TC12
MANUFACTURER      DURCEL
DESCRIPTION      BATTERY,COPPERTP AAA BUL
UOM      CARTON
QTY      10
NUMBER      3
PART NUMBER      E91SBP36H
MANUFACTURER      UNICAR
DESCRIPTION      BATTERY,ENERGIZER,AA,36P
UOM      PACK
QTY      40
NUMBER      4
PART NUMBER      E91SF24
MANUFACTURER      UNICAR
DESCRIPTION      BATTERY,ENRGZ,AA,24PK...
UOM      PACK
QTY      40

I need to transpose the rows into 6 separate columns so that the data now looks like this:

1      F5D8053      BELKIN      ADAPTER,N WIRELESS USB,G      EACH      10
2      MN2400TC12      DURCEL      BATTERY,COPPERTP AAA BUL      CARTON      10
3      E91SBP36H      UNICAR      BATTERY,ENERGIZER,AA,36P      PACK      40
4      E91SF24      UNICAR      BATTERY,ENRGZ,AA,24PK...      PACK      40

I could go through the paste "transpose" exercise, but I would have to do it more than 1000 times. I had a similar problem a few years back, but the number of repeating rows was 8 instead of 6 and I got this great solution as follows:

"Assuming that the field names are in column A and the field values are in column B, both starting from row 1, then enter this formula

=OFFSET($B$1,ROW()*8+COLUMN()-20,0)

in cell D2 and then copy across and down.

You will have to adjust the formula if the locations are differend than what I have assumed. If you have any difficulties in doing that then you can come back and ask.

Saqib"

I tried to modify this equation to account for the 6 rows rather than 8, but I can't seem to get it to work. Guess I just don't grasp the offset function used here. Can anyone help me please? Thanks. - Tom

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

Just in case you come across this again, this is what it does

D2: =OFFSET($B$1,ROW()*6+COLUMN()-16,0)
becomes =OFFSET($B$1,2*6+4-16,0) ' D2 row=2, col=4
becomes =OFFSET($B$1,0,0) ' so moving 0 rows from B1 is just B1

E2: =OFFSET($B$1,ROW()*6+COLUMN()-16,0)
becomes =OFFSET($B$1,2*6+5-16,0) ' D2 row=2, col=5
becomes =OFFSET($B$1,1,0) ' so moving 1 rows from B1 is B2, one lower due to us moving one column to the right

D3: =OFFSET($B$1,ROW()*6+COLUMN()-16,0)
becomes =OFFSET($B$1,3*6+4-16,0) ' D3 row=3, col=4
becomes =OFFSET($B$1,6,0) ' so moving 6 rows from B1 is B7, for each row we move in the transposed area, we are starting 6 rows down (due to multiplying ROW() by 6)

barry houdini

Assuming the data is in B1:B1000 then put this formula in D2 and copy across to to I2 and down as far as required
=INDEX($B$1:$B$1000,COLUMNS($D2:D2)+(ROWS(D$2:D2)-1)*6)
adjust refs as required
regards, barry

cyberkiwi

Barry,

Nice formula.
Doesn't it run out (#REF) after 167 records due to 167*6 > 1000?
Sticking with your version, would

=INDEX($B:$B,COLUMNS($D2:D2)+(ROWS(D$2:D2)-1)*6)

be better?

Tom Farrar

ASKER

Absolutely perfect. What is the significance of the changes to the column() from -20 to -16, if you don't mind me asking? Thanks a bunch for your help on this. I needed it very much late in the day. - Tom

barry houdini

My suggested solution allows you to easily base the formula on 3 things, data range, start cell, number of columns. If any of those changes you simply adjust accordingly.
INDEX is also more efficient than volatile OFFSET, and using COLUMNS and ROWS rather than COLUMN and ROW makes the formula more robust, e.g. adding rows above or columns to the left won't effect the formula results
regards, barry

cyberkiwi

>> What is the significance of the changes to the column() from -20 to -16

Have a look at http:#a32956922
It gets the formula working right! :)

Regards

barry houdini

Hello Cyberkiwi,
Yes, my suggestion was for a sample range of 1000 cells, of course you can change that or make it the whole column......
regards, barry

cyberkiwi

Hi Barry,

I've heard really good things about you, so I knew you had a good reason for upheaving a working formula. Yes, I forgot about the volatile nature of OFFSET.

It took me a while to decipher COLUMNS/ROWS rather than the singular COLUMN and ROW + OFFSET version.

Regards

barry houdini

Hello cyberkiwi,
I doubt you need many tips from me - seem to be making some history of your own here at EE...nice work
regards, barry

Tom Farrar

ASKER

Thanks for all the input and education on the offset and index functions, etc....