Sub arrange_columns()
Dim a As Variant, b As Variant, dic As Object
Dim i As Long, j As Long, k As Long, lr As Long, m As Long, n As Long
lr = Range("A" & Rows.Count).End(3).Row
a = Range("A2:C" & lr).Value2
Set dic = CreateObject("Scripting.Dictionary")
'The following formula calculates the number of columns that the output will have
'It gets the maximum number of times that a seller appears,
'in my example John appears 4 times, multiplies 4 * 2,
'we need 2 columns one for the Invoice and one for the Amount;
'and I add 1 column for the vendor.
'So in this example the maximum of columns is 4 * 2 + 1 = 9
n = Evaluate("=MAX((COUNTIF(A2:A" & lr & ",A2:A" & lr & ")))") * 2 + 1
'Redeem the size of the storage capacity of variable b
'(number of rows starting at 1 and up to the number of elements that variable a has.
'In this example it has 8 elements, although there are repeated values,
'the maximum number of rows that could exist are 8.
'And the number of columns starts at 1 and up to n)
'redim b(1 to 8, 1 to 9)
ReDim b(1 To UBound(a), 1 To n)
m = 1
For i = 1 To UBound(a, 1)
If Not dic.exists(a(i, 1)) Then
'If the vendor does not exist in the index(dictionary),
'then increase the row number(m),
m = m + 1
'add the vendor to the index, but put the data: row and column into that index.
dic(a(i, 1)) = m & "|" & 3
'The first time the row is 1, then in b(1, 1) stores the vendor.
'In b(1, 2) the invoice and in b(1, 3) the amount.
b(m, 1) = a(i, 1)
b(m, 2) = a(i, 2)
b(m, 3) = a(i, 3)
Else
'If the vendor does not exist, then
'split the contents of the index,
'but in the index I do not have the invoice or the amount stored,
'what I stored in the index was the row number that corresponds
'(in the output) to the vendor and how many columns it has used
'
'For John's example, (remember) we store 1 "|" 3.
'So k = 1
'j = 3
k = Split(dic(a(i, 1)), "|")(0)
j = Split(dic(a(i, 1)), "|")(1)
'
'This is to put the heading
b(1, j + 1) = "Invoice"
b(1, j + 2) = "Amount"
'
'In b(k, j + 1) that is b(1, 3 + 1) that is b(1, 4) I put the invoice
'from a(i, 2) and in b(1, 5) I put the amount from a(i, 3)
b(k, j + 1) = a(i, 2)
b(k, j + 2) = a(i, 3)
'I update the column in John's index, the row is still 1,
'but the column is now j + 2 = 3 + 2 = 5
dic(a(i, 1)) = k & "|" & j + 2
End If
Next
Range("E2", Cells(Rows.Count, Columns.Count)).ClearContents
Range("E1").Resize(m, n).Value = b
Range("E1:G1").Value = Range("A1:C1").Value
End Sub
.
