0

I would like to transform this:

id  brand color price
1   bmw   white 100
2   fiat  black 200
3   audi  blue  300

into this

id  value
1   bmw
1   white
1   100
2   fiat
2   black
2   200
3   audi
3   blue
3   300

Headers (id, title...) are not important, all I need are values.

I've been searching for a solution for some time now, but can't find any.

Thanks for you help!

weezle
  • 79
  • 1
  • 6
  • 15
  • 1
    Take a look at this [SO post](http://stackoverflow.com/q/10921791/293078). You could run something like it and then delete the Header column. – Doug Glancy Jun 04 '13 at 13:41

1 Answers1

2

You can do this with Excel formulas. Assuming that your data are in cells 'A2:D4' and that your results begin cell F2, the following OFFSET formulas will transform the data as you want:

The first formula would begin in cell F2. This is the formula that gets and repeats the id:

  =OFFSET($A$1,INT((ROW(F2)-ROW($F$2))/3+1),0)

The second formula would begin in cell G2. This one traverses the columns of the data:

  =OFFSET($B$1,F2,MOD(ROW(F2)-ROW($F$2),3))

Both would be copied down as far as would be needed to transpose all the data.

Excel sheet display

chuff
  • 5,846
  • 1
  • 21
  • 26