Pivot data using VBA

Question

I have data in the following format

Col1     Col2    Col3    Col4
ServerA  1002    CPU     1
ServerA  1003    Cores   4
ServerA  1005    Memory  16

I need the data to appear as

Col1     Col2     Col3     Col4     Col5     Col6    Col7   Col8     Col9
ServerA  1002     CPU      1003     Cores    4       1005   Memory   16

Is there a way to achieve this using Excel VBA?

@Tim Williams - Apologies. I have updated the post. All the fields need to appear. — PeanutsMonkey, May 05 '14 at 05:35
And there are varying count of `ServerA`? I mean it's not always 3 right? There can be 4 `ServerA` then 5 `ServerB` for example? — L42, May 05 '14 at 05:43
Ok so the answer to your question `Is there a way to achieve this using Excel VBA?` is yes :) — L42, May 05 '14 at 06:56
@L42 - I thought it would be and it could be interpreted as how so? — PeanutsMonkey, May 05 '14 at 07:16
See my post. Not sure if that is the fastest and the most effective way to do it though. :) — L42, May 05 '14 at 07:37

score 1 · Accepted Answer · answered May 05 '14 at 07:36

You can try this:
Not very clean but I think it should serve your purpose.

Option Explicit

Sub Sample()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim RngToUnstack As Range, cel As Range, cel1 As Range
Dim i As Long

Set ws1 = ThisWorkbook.Sheets("Sheet2")
Set ws2 = ThisWorkbook.Sheets("Sheet3")

Set RngToUnstack = ws1.UsedRange
'~~> just an alternative to .UsedRange
'Set RngToUnstack = ws1.Range("A1", "D" & ws1.Range("A" & _
    ws1.Rows.Count).End(xlUp).Row)

'~~> construct your unique ID's in Worksheet 2
With ws2
    RngToUnstack.Resize(, 1).Copy .Range("A1")
    .Range("A1", .Range("A" & .Rows.Count).End(xlUp).Address).RemoveDuplicates 1, xlNo
End With
'~~> loop to populate the ID's
For Each cel1 In ws2.Range("A1", ws2.Range("A" & ws2.Rows.Count).End(xlUp).Address)
    i = 0
    For Each cel In RngToUnstack.Resize(, 1)
        If cel.Value = cel1.Value Then
            cel.Resize(, 3).Offset(0, 1).Copy cel1.Offset(0, (3 * i) + 1)
            i = i + 1
        End If
    Next cel
Next cel1
End Sub

Result:

Suppose your sample data looks like this:
enter image description here

After running the macro will become like this:
enter image description here

Pivot data using VBA

1 Answers1

Linked