Split records

[vmjan02]

I have this table and trying to get the outpus , but no luck tried different ways, as well.
Any vba to get the results.

NE	Port Full Name	OutPut1	OutPut2	OutPut3	OutPut4	OutPut5	OutPut6	OutPut7	OutPut8
103-00_BDEHRD00OL0	Frame:0/Slot:19/Port:0	Frame:0/Slot:19/Port:0	Frame:0/Slot:20/Port:0
103-00_BDEHRD00OL0	Frame:0/Slot:20/Port:0
104-00_GBIRRD00OL0	Frame:0/Slot:19/Port:0	Frame:0/Slot:19/Port:0	Frame:0/Slot:20/Port:0
104-00_GBIRRD00OL0	Frame:0/Slot:20/Port:0
104-00_GBIRRD00OL1	Frame:0/Slot:9/Port:0	Frame:0/Slot:9/Port:0	Frame:0/Slot:9/Port:1	Frame:0/Slot:9/Port:2	Frame:0/Slot:9/Port:3	Frame:0/Slot:19/Port:0	Frame:0/Slot:20/Port:0
104-00_GBIRRD00OL1	Frame:0/Slot:9/Port:1
104-00_GBIRRD00OL1	Frame:0/Slot:9/Port:2
104-00_GBIRRD00OL1	Frame:0/Slot:9/Port:3
104-00_GBIRRD00OL1	Frame:0/Slot:19/Port:0
104-00_GBIRRD00OL1	Frame:0/Slot:20/Port:0
104-00_GBIRRD00OL5	Frame:0/Slot:9/Port:0
104-00_GBIRRD00OL5	Frame:0/Slot:9/Port:1
104-00_GBIRRD00OL5	Frame:0/Slot:10/Port:0
104-00_GBIRRD00OL5	Frame:0/Slot:10/Port:1
105-00_YMAMRD00OL0	Frame:0/Slot:9/Port:0
105-00_YMAMRD00OL0	Frame:0/Slot:9/Port:1
105-00_YMAMRD00OL0	Frame:0/Slot:9/Port:2
105-00_YMAMRD00OL0	Frame:0/Slot:9/Port:3
105-00_YMAMRD00OL0	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OL0	Frame:0/Slot:20/Port:0
105-00_YMAMRD00OL1	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OL1	Frame:0/Slot:20/Port:0
105-00_YMAMRD00OL2	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OL2	Frame:0/Slot:20/Port:0
105-00_YMAMRD00OL5	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OL5	Frame:0/Slot:20/Port:0
105-00_YMAMRD00OL7	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OL7	Frame:0/Slot:20/Port:0
105-00_YMAMRD00OL8	Frame:0/Slot:10/Port:0
105-00_YMAMRD00OL8	Frame:0/Slot:10/Port:1
105-00_YMAMRD00OL8	Frame:0/Slot:10/Port:2
105-00_YMAMRD00OL8	Frame:0/Slot:10/Port:3
105-00_YMAMRD00OL8	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OL8	Frame:0/Slot:19/Port:1
105-00_YMAMRD00OL8	Frame:0/Slot:20/Port:0
105-00_YMAMRD00OL8	Frame:0/Slot:20/Port:1
105-00_YMAMRD00OL9	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OL9	Frame:0/Slot:20/Port:0
105-00_YMAMRD00OLA	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OLA	Frame:0/Slot:20/Port:0
105-00_YMAMRD00OLC	Frame:0/Slot:19/Port:0
105-00_YMAMRD00OLC	Frame:0/Slot:20/Port:0

 

[DanteAmor]

Assuming the records start in cell A2, the results in cell C2 onwards.

Try this:

Sub split_records()
  Dim dic As Object
  Dim nCol As Long, nRow As Long, i As Long
  Dim a As Variant, b As Variant
 
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("B" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 100)
 
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      dic(a(i, 1)) = i
      nCol = 1
    Else
      nCol = nCol + 1
    End If
    b(dic(a(i, 1)), nCol) = a(i, 2)
  Next
 
  Range("C2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[vmjan02]

Perfect, on target, thanks a ton. saved a lot of time.