Transposing columns to data groups with matching data

[StioB]

Hi there,


I have a two columns. One with a group (in this case colour) and the other column with a number. I want to transpose the data so that I have multiple columns based on the groups in the first column.

Could someone point how to:

• Either get this format from pivot tables (I tried but couldn’t get the values in the cells of the table).
• Describe the macro VBA code to produce this.

So from this:

colour	number
blue	65​
black	63​
red	43​
yellow	96​
green	97​
purple	52​
pink	48​
blue	22​
black	69​
red	29​
yellow	32​
green	62​
purple	53​
pink	34​
blue	75​
black	60​
red	47​
yellow	33​
green	36​
purple	65​
pink	42​

To this:

black	blue	green	pink	purple	red	yellow
63​	65​	97​	48​	52​	43​	96​
69​	22​	62​	34​	53​	29​	32​
60​	75​	36​	42​	65​	47​	33​

Thanks, StioB

 

[DanteAmor]

Hi and welcome to MrExcel

Try this

Sub Transposing_Columns()
  Dim a As Variant, dic As Object
  Dim i As Long, col As Long, lin As Long
 
  a = Range("A2", Range("B" & Rows.Count).End(3)).Value2
  ReDim b(1 To UBound(a), 1 To UBound(a))
  Set dic = CreateObject("Scripting.Dictionary")
 
  For i = 1 To UBound(a)
    If Not dic.Exists(a(i, 1)) Then
      col = col + 1
      lin = 1
    Else
      col = Split(dic(a(i, 1)), "|")(0)
      lin = Split(dic(a(i, 1)), "|")(1) + 1
    End If
    dic(a(i, 1)) = col & "|" & lin
    b(lin, col) = a(i, 2)
  Next
  Range("D1").Resize(1, dic.Count).Value = dic.keys
  Range("D2").Resize(UBound(a), UBound(a)).Value = b
  Range("D1", Cells(UBound(a), UBound(a))).Sort key1:=Range("D1"), order1:=xlAscending, Header:=xlNo, Orientation:=xlSortRows
End Sub

 

[StioB]

Thanks DanteAmor,

That is great. Working from that, I have a few columns which are similar but the number of categories in the first column are not always the same.

Can a macro be developed so that it can automatically detect the number of categories in the first column and assigns the matching numbers in the second column to the categories, even if the groups change in the column?

Kind regards,

StioB

 

[DanteAmor]

That is great. Working from that, I have a few columns which are similar but the number of categories in the first column are not always the same.

Can a macro be developed so that it can automatically detect the number of categories in the first column and assigns the matching numbers in the second column to the categories, even if the groups change in the column?

I did not understand, you could explain that with examples and expose what result you want.
Use XL2BB tool to put samples of data, see my signature.