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+COLUM
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
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+COLUM
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
Just in case you come across this again, this is what it does
D2: =OFFSET($B$1,ROW()*6+COLUM
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+COLUM
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+COLUM
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)
D2: =OFFSET($B$1,ROW()*6+COLUM
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+COLUM
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+COLUM
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)
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,COLUMN
adjust refs as required
regards, barry
=INDEX($B$1:$B$1000,COLUMN
adjust refs as required
regards, barry
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:D
be better?
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:D
be better?
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
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
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
>> 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
Have a look at http:#a32956922
It gets the formula working right! :)
Regards
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
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial