Sub transe()
Dim a, z, i As Long, j As Long, Y, n&, x, k, jj&
Const delim As String = ","
With Sheets("Sheet1")
a = .Range("A2").CurrentRegion 'click on cell A2, and press Ctrl + A.
'this selects the "current region" and sets the
'values of this range as an array into variable 'a'
End With
With CreateObject("Scripting.Dictionary") 'go search it up on this
.comparemode = 1 'compares text so "AAA" is equals to "aaa"
'i.e. case-insensitive
ReDim Y(1 To UBound(a, 1), 1 To 2) 'dim variable Y with the number of rows you have, and with 2 columns
For i = 2 To UBound(a) 'loop through the rows
If Not .Exists(a(i, 1)) Then 'check if a unique key entry exists in the dictionary object
'let's call this KEY1
'does not exist
ReDim z(1 To UBound(a, 2)) 'dim variable z to the maximum number of columns you have
For j = 1 To UBound(a, 2) 'loop through the columns
z(j) = a(i, j) 'put into array z each data you have (through the columns)
Next
.Item(a(i, 1)) = Join(z, delim) 'join up the array z with the delimiter as defined above ","
'e.g. abc,def,ghi,jkl,,,, is now stored in KEY1
Else
'exists
ReDim z(1 To UBound(a, 2)) 'same as above
For j = 2 To UBound(a, 2) 'same as above
z(j) = a(i, j) 'same as above
Next
.Item(a(i, 1)) = .Item(a(i, 1)) & "," & Join(z, delim)
'since a unique key exists, it now adds what was joined into existing data
'e.g. abc,def,ghi,jkl,,,,opq,rst,uvw,xyz,,,, is now stored in KEY1
End If
Next
z = .items 'variable z now holds all items stored (with "," delimiter)
For i = 0 To UBound(z) 'loop through the items
x = Split(z(i), delim) 'split each item z holds
'e.g. x now holds
' "abc"
' "def"
' "ghi"
' "jkl"
' ..etc.
n = n + 1 'row counter
jj = 0 'reset column counter (on each row looped)
For k = 0 To UBound(x) 'loop through x. i.e. "abc" first, then "def", then "ghi", etc.
If x(k) <> vbNullString Then 'check that it's not a blank
If jj >= UBound(Y, 2) Then ReDim Preserve Y(1 To UBound(a, 1), 1 To jj + 1)
'jj is the column counter, if there is more columns than the array Y
'currently holds, "upsize" array Y to fit
jj = jj + 1
Y(n, jj) = x(k) 'we know that variable Y has the same number of rows you have
'and just enough columns to fit the largest row with most columns
'this now puts data into Y
'e.g.
'KEY1 "abc" "def" "ghi" "jkl" "opq" "rst" "uvw" "xyz"
End If
Next
Next
With Sheets("Sheet2")
.UsedRange.Offset(1).ClearContents 'go to sheet2, select your "used range" and move 1 row down. i.e. cell A2 onwards
'and clear whatever the cells contains
.Range("A2").Resize(n, UBound(Y, 2)) = Y 'from A2, select the size (rows & columns) that you have in Y and sets the values in
.Columns.AutoFit
End With
End With
End Sub
HTH
Bookmarks