Find and merging duplicate data. Make it a column name followed by its data respectively-CodePudding

Im a student and I need a help. I have a repeated text data from column x, and I want it to become a header then its table content from data in the column y respectively match by data column x. Im currently using an excel and you guys may recommend what else easier tools that I can use.

So basically i want to make a table from the left table to be like the right table

CodePudding user response：

Assuming your data are stored in column A and B, try this formula:

=AGGREGATE(15,6,ROW($A:$A)/($A:$A=E$1,ROW(E2)-ROW($E$2) 1)

CodePudding user response：

Please, try the next VBA code. It uses a Scripting Dictionary loaded with column x as keys and column y as items. Using arrays and the mentioned dictionary, it mostly works in memory and should be very fast:

Sub UniqueDataValues()
  Dim sh As Worksheet, lastR As Long, arr, arrFin, i As Long, maxCol As Long, dict As Object
  
  Set sh = ActiveSheet  'use here the sheet you need
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).row 'last row
  
  arr = sh.Range("A2:B" & lastR).Value2  'place the range in an array for faster iteration
  
  'load the dictionary:
  Set dict = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(arr)
       dict(arr(i, 1)) = dict(arr(i, 1)) & "|" & arr(i, 2)
  Next i

  ReDim arrFin(1 To dict.count, 1 To UBound(arr))
  Dim k As Long, key, arrIt
  k = 1
  For Each key In dict.Keys
     arrIt = Split(dict(key), "|")
     If UBound(arrIt)   1 > maxCol Then maxCol = UBound(arrIt)   1
     arrFin(k, 1) = key
     For i = 1 To UBound(arrIt)
           arrFin(k, 1   i) = arrIt(i)
     Next i
     k = k   1
  Next key
  
  'drop the final array content at once:
  ReDim Preserve arrFin(1 To dict.count, 1 To k - 1)
  With sh.Range("J2").Resize(dict.count, k - 1)
    .Value2 = Application.Transpose(arrFin)
    .EntireColumn.AutoFit
 End With
End Sub

Please, send some feedback after testing it.

If something unclear, do not hesitate to ask for clarifications...