Excel Refactoring Using a Formula/Macro

Question

I have an excel table that looks like this:

I need to be able to reformat it automatically like so:

I've tried every possible formula, and every possible variation of a pivot table but can't find anything that even remotely gets me to where I want to be. For example, adding Vlookups in additional columns and then generating 4 tables and merging is too manual for this to be workable. Tried using index/match, that also didn't work out as it is too manual. In real life, this table would vary in size, so adjusting the "reformatted" table is not really optimum. Any ideas, suggestions are more than welcome!

Possible duplicate of [Unpivot an Excel matrix/pivot-table?](http://stackoverflow.com/questions/32115219/unpivot-an-excel-matrix-pivot-table). — caiohamamura, Dec 11 '15 at 12:55

score 1 · Accepted Answer · answered Dec 11 '15 at 14:06

1

With data like:

This short macro:

Sub ReOrganizeData()
Dim i As Long, j As Long, K As Long, N As Long
Dim v As Variant

N = Cells(Rows.Count, "A").End(xlUp).Row
K = 2
For j = 2 To 5
   For i = 3 To N
      v = Cells(i, j)
      If v <> "" Then
         Cells(K, "H") = Cells(i, "A")
         Cells(K, "I") = v
         Cells(K, "J") = Cells(2, j)
         K = K + 1
      End If
   Next i
Next j
End Sub

will produce:

answered Dec 11 '15 at 14:06

Gary's Student

95,722
10
59
99

That's very good! Does exactly the job it's supposed to! Cheers! – Yavor Dec 11 '15 at 15:05

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

It is possibly with basically the process detailed here. However in your case you will need to switch the order of Column and Value in the resulting Table, Convert to Range to be able to sort on Row within Column and also filter to select and delete blank rows from Value. Also you will need to change the column labels.

edited May 23 '17 at 11:59

Community

1
1

answered Dec 11 '15 at 13:09

pnuts

58,317
11
87
139

Excel Refactoring Using a Formula/Macro

2 Answers2