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