We help IT Professionals succeed at work.
Get Started

Rows To Columns...

Tom Farrar
Tom Farrar asked
on
383 Views
Last Modified: 2012-06-27
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


Comment
Watch Question
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE