Transposing Data

[Jerry138889]

Hi

I haven't been able to figure out how to phrase this to utilise the search here or on Google to find the answer, although I'm sure it is fairly simple.

I have data in the following format:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Company[/TD]
[TD="class: xl64, width: 64"] Rate[/TD]
[/TR]
[TR]
[TD="class: xl63"]AAA[/TD]
[TD="class: xl63"]Rate 1[/TD]
[/TR]
[TR]
[TD="class: xl63"]AAA[/TD]
[TD="class: xl63"]Rate 2[/TD]
[/TR]
[TR]
[TD="class: xl63"]AAA[/TD]
[TD="class: xl63"]Rate 3[/TD]
[/TR]
[TR]
[TD="class: xl63"]BBB [/TD]
[TD="class: xl63"]Rate 1[/TD]
[/TR]
[TR]
[TD="class: xl63"]BBB [/TD]
[TD="class: xl63"]Rate 2[/TD]
[/TR]
[TR]
[TD="class: xl63"]CCC[/TD]
[TD="class: xl63"]Rate 1[/TD]
[/TR]
[TR]
[TD="class: xl63"]CCC[/TD]
[TD="class: xl63"]Rate 2[/TD]
[/TR]
[TR]
[TD="class: xl63"]CCC[/TD]
[TD="class: xl63"]Rate 3[/TD]
[/TR]
[TR]
[TD="class: xl63"]CCC[/TD]
[TD="class: xl63"]Rate 4[/TD]
[/TR]
</tbody>[/TABLE]

and I want it to be displayed in the following format

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Company[/TD]
[TD="class: xl66, width: 64"] Rate[/TD]
[TD="class: xl66, width: 64"]Rate[/TD]
[TD="class: xl66, width: 64"]Rate[/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]AAA[/TD]
[TD="class: xl65"]Rate 1[/TD]
[TD="class: xl65"]Rate 2[/TD]
[TD="class: xl65"]Rate 3
[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]
BBB

[/TD]
[TD="class: xl65"]Rate 1[/TD]
[TD="class: xl65"]Rate 2[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]CCC[/TD]
[TD="class: xl65"]Rate 1[/TD]
[TD="class: xl65"]Rate 2[/TD]
[TD="class: xl65"]Rate 3[/TD]
[TD="class: xl65"]
Rate 4

[/TD]
[/TR]
</tbody>[/TABLE]


Sort of like an advanced transpose function. A pivot doesn't seem to do it correctly.

Thanks for the help

 

[Fluff]

How about

Sub ConsolidateTranspose()

   Dim Cl As Range
   Dim Itm As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1).Value
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & "|" & Cl.Offset(, 1).Value
         End If
      Next Cl
      Range("A1").CurrentRegion.Offset(1).Clear
      Range("A2").Resize(.Count).Value = Application.Transpose(.keys)
      For Each Itm In .Items
         Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(Split(Itm, "|")) + 1).Value = Split(Itm, "|")
      Next Itm
   End With
End Sub

 

[Jerry138889]

How about

Sub ConsolidateTranspose()

   Dim Cl As Range
   Dim Itm As Variant
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1).Value
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & "|" & Cl.Offset(, 1).Value
         End If
      Next Cl
      Range("A1").CurrentRegion.Offset(1).Clear
      Range("A2").Resize(.Count).Value = Application.Transpose(.keys)
      For Each Itm In .Items
         Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(Split(Itm, "|")) + 1).Value = Split(Itm, "|")
      Next Itm
   End With
End Sub

Fluff, that's perfect. I thought there may have been a way to do it outside of VBA but your code works wonderfully.

 

[Fluff]

Glad to help & thanks for the feedback.

There probably is a way to do it using formulae, but I don't know how.