CODEWITHSUNDEEP
excel
Yavor
Yavor

Reputation: 703

Excel Refactoring Using a Formula/Macro

I have an excel table that looks like this:

enter image description here

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

enter image description here

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!

Upvotes: 0

Views: 1370

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

With data like:

enter image description here

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:

enter image description here

Upvotes: 1

pnuts
pnuts

Reputation: 59460

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.

Upvotes: 0

Related Questions