Split Records

[vmjan02]

I have this data,

Fixed FTTH CBU Tech Compliants V1.xlsm
	B	C	D
24	Node*+	Root Cause	Ticket Number+
25	218-00_SAFAKH00OL0	Access-Uplink issue	PR00035914963
26			PR00035915931
27		Dark Fiber	PR00035913520
28			PR00035914983
29			PR00035916061
30			PR00035917254
31			PR00035920093
32		MDT	PR00035931421
33			PR00035931520
34			PR00035933798
35			PR00035933799
36			PR00035933811
37	116-42_NFELRDAAOLK	Man Made	PR00035951924
38	111-00_HTYNRD00OL5	MDT	PR00035909693
39			PR00035923231
40		Trans Media	PR00035963757
41	116-42_NFELRDAAOT1	Trans Media	PR00035967382
OLT2

and I have this code

Sub split_recordsOLT2()
  Dim dic As Object
  Dim nCol As Long, nRow As Long, i As Long
  Dim a As Variant, b As Variant
 
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 '''''
 ''''' OLT2
 '''''
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("C25", Range("D" & 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("E25").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

but I am looking for this output, any modification on the code.

Fixed FTTH CBU Tech Compliants V1.xlsm
	B	C	D	E	F	G	H	I
24	Node*+	Root Cause	Ticket Number+	Output1	Output2	Output3	Output4	Output5
25	218-00_SAFAKH00OL0	Access-Uplink issue	PR00035914963	PR00035914963	PR00035915931
26			PR00035915931
27		Dark Fiber	PR00035913520	PR00035913520	PR00035914983	PR00035916061	PR00035917254	PR00035920093
28			PR00035914983
29			PR00035916061
30			PR00035917254
31			PR00035920093
32		MDT	PR00035931421	PR00035931421	PR00035931520	PR00035933798	PR00035933799	PR00035933811
33			PR00035931520
34			PR00035933798
35			PR00035933799
36			PR00035933811
37	116-42_NFELRDAAOLK	Man Made	PR00035951924	PR00035951924
38	111-00_HTYNRD00OL5	MDT	PR00035909693	PR00035909693	PR00035923231
39			PR00035923231
40		Trans Media	PR00035963757	PR00035963757
41	116-42_NFELRDAAOT1	Trans Media	PR00035967382	PR00035967382
OLT2

 

[vmjan02]

Debug.Print .Address

 

[Alex Blakenburg]

Please show me all your code that debug.print line shouldn't be able to produce that output.

 

[JEC]

you probably have other data next to your data

What if you write the with line like

With Range("B24", Range("D" & Rows.Count).End(xlup)).Resize(, 4)

 

[vmjan02]

perfect got it