excel - transpose (rows to columns) with extra dimension?

Question

is it possible to transpose rows to columns with extra dimensions??

imagine table:

dimension |  data 1 |  data 2 | data 2
---------------------------------------
  test    |     a   |    b    |
  dummy   |     c   |    e    |   x
  user    |     a   |    d    |
john doe  |     v   |         |

I need to get this:

dimension |  data 
-------------------
  test    |     a  
  test    |     b  
  dummy   |     c
  dummy   |     e
  dummy   |     x
  user    |     a
  user    |     d
john doe  |     v

Is there any built-in function? Or do I need to use macro?

Possible duplicate of [Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')](http://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal) — CallumDA, Apr 05 '17 at 09:53

score 0 · Answer 1 · answered Apr 05 '17 at 09:17

Obviously this is more straightforward with VBA, though it can be done using worksheet formulas along, viz:

Assuming your source table is in A1:D5 (with headers in row 1), enter this array formula** in F2:

=IF(ROWS($1:1)>COUNTIF(B$2:D$5,"<>"),"",INDEX(A$2:A$5,MATCH(ROWS($1:1),1+MMULT(0+(ROW(B$2:D$5)>TRANSPOSE(ROW(B$2:D$5))),MMULT(0+(B$2:D$5<>""),TRANSPOSE(COLUMN(B$2:D$5)^0))))))

Copy down until you get blanks for the results.

Then enter this formula in G2:

=IF(F2="","",INDEX(INDEX(B$2:D$5,MATCH(F2,A$2:A$5,0),),COUNTIF(F$2:F2,F2)))

Copy down as required.

Note that this set-up assumes that it is not possible for a given dimension to have blank entries in between non-blank entries within the data columns, for example data1 and data3 are non-blank but data2 is blank. This is the case for the data you provided so seems like a fair assumption.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

excel - transpose (rows to columns) with extra dimension?

1 Answers1