Converting List of Values to Records

[SBest]

I have a large list of data - order numbers and items. There could be anything from 1 to 200 items per order. I am trying to convert this so that each order number is a single row, with relevant items listed horizontally. So, each order number would be a record with between 1 and 200 items listed against it. I feel like there is a simple soution that I'm missing, but I can't find it and am struggling to put it in the right wording to find the answer online. I have uploaded a simplified example - my data as it is in columns A:B - I am trying to get to something like D:H

Book3
	A	B	C	D	E	F	G	H
1	Item	Order		Order
2	X	1		1	X	Y	Z
3	Y	1		2	A	B	C	X
4	Z	1
5	A	2
6	B	2
7	C	2
8	X	2
9
Sheet1

 

[Marc L]

According to your attachment a VBA demonstration as a beginner starter (data must be sorted on column B) :​

Sub Demo1()
        Dim Rg(1) As Range, R&
        Set Rg(1) = [B1]:   R = 1
        [D1].CurrentRegion.Offset(1).Clear
        Application.ScreenUpdating = False
    With [A1].CurrentRegion.Columns(2)
      Do
            Set Rg(0) = Rg(1)(2)
            Set Rg(1) = .Find(Rg(0).Text, , , 1, , 2)
            R = R + 1
            Cells(R, 4).Value2 = Rg(0).Value2
        With Range(Rg(0), Rg(1))
            Cells(R, 5).Resize(, .Rows.Count).Value2 = Application.Transpose(.Columns(0))
        End With
      Loop Until Rg(1).Row = .Rows.Count
    End With
        Application.ScreenUpdating = True
        Erase Rg
End Sub

 

[SBest]

Thanks for the reply Marc. It works great with the sample data, but I should probably have mentioned that my real data set is approx 600k rows. So when I run that VBA on it, I just eventually crash Excel. Anyhting else I can try, or am I just going to need to find a way to reduce my data-set?

 

[Fluff]

Hi & welcome to MrExcel.
Will the orders in col B always be grouped together?

 

[SBest]

Hi & welcome to MrExcel.
Will the orders in col B always be grouped together?

As in the data sorted by Coloumn B? If so, yes.

 

[Fluff]

Ok, how about

Sub SBest()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, nc As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary), 1 To 300)
   
   For r = 2 To UBound(Ary)
      If Ary(r, 2) <> Ary(r - 1, 2) Then
         nr = nr + 1
         Nary(nr, 1) = Ary(r, 2)
         Nary(nr, 2) = Ary(r, 1)
         nc = 3
      Else
         nc = nc + 1
         Nary(nr, nc) = Ary(r, 1)
      End If
   Next r
   Sheets("Sheet1").Range("D2").Resize(nr, 300).Value = Nary
End Sub

 

[SBest]

Ok, how about

Sub SBest()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, nc As Long
  
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary), 1 To 300)
  
   For r = 2 To UBound(Ary)
      If Ary(r, 2) <> Ary(r - 1, 2) Then
         nr = nr + 1
         Nary(nr, 1) = Ary(r, 2)
         Nary(nr, 2) = Ary(r, 1)
         nc = 3
      Else
         nc = nc + 1
         Nary(nr, nc) = Ary(r, 1)
      End If
   Next r
   Sheets("Sheet1").Range("D2").Resize(nr, 300).Value = Nary
End Sub

I get an immediate "Run-time error '7': Out of Memory"

 

[Fluff]

Do you only have two columns of data on the sheet?

 

[SBest]

Do you only have two columns of data on the sheet?

Yes. This is a sample of the real data, just in case that is useful

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Item	Order
2	BJT053-1	316440563
3	BJT107-1	316440563
4	BJT113-1	316440563
5	BJT104-1	316440563
6	BJT102-1	316440563
7	BJT110-1	316440563
8	BJT104-2	316440563
9	BJT104-3	316440563
10	BJT107-2	316440563
11	BJT101-1	316440563
12	BJT100-1	316440563
13	BJT108	346279439
14	BJT109	346279439
15	BJT174	346279439
16	BJT161	346279439
17	BJT104-3	346279439
18	BJT104	346279439
19	BJT110	346279439
20	BJT105	346279439
21	BJT118	346279439
22	BJT192	346279439
23	BJT052	346279439
24	BJT057	346279439
25	BJT016	346279439
26	BJT038	346279439
27	BJT103	346279439
28	BJT182	346279439
29	BJT189	346279439
30	BJT193	346279439
31	BJT237	346279439
32	BJT250	346279439
33	BJT273	346279439
34	BJT306	346279439
35	BJT308	346279439
36	BJT451	346279439
37	BJT465	346279439
38	BJT480	346279439
39	BR109	346279439
40	T0203	346286089
41	ST155	346286102
42	T0148	346286102
43	T0217	346286102
44	T0219	346286102
45	T0068	346286102
46	T0023	346286161
47	T0095	346286161
48	T0111	346286161
49	GTSUBB1032	346286161
50	T0223	346286161
51	T0224	346286161
52	GTRBH11155	346286161
53	T0050	346286242
54	T0011	346286242
55	GTSUBB1032	346286242
56	T0215	346286242
57	T0222	346286242
58	SCFBE80	346286242
59	T0148	346286294
60	T0210	346286294
61	T0217	346286294
62	T0219	346286294
63	T0068	346286294
64	T0226	346286294
65	T0201	346286353
66	T0152	346286366
67	GTSUBB1032	346286366
68	T0224	346286366
69	GTRBH11155	346286366
70	T0410	346286366
71	T0201	346286414
72	GTAIRR1026	346286427
73	T0221	346286427
74	GTHELB1060	346286427
75	T0057	346286454
76	T0212	346286454
77	T0213	346286454
78	T0214	346286454
79	T0219	346286454
80	T0068	346286454
81	T0168	346286454
82	T0227	346286454
83	T0226	346286454
84	T0145	346286454
85	T0159	346286454
86	T0403	346286454
87	T0303	346286454
88	GTAIRR1026	346286566
89	RB20073	346286566
90	T0151	346286566
91	T0221	346286566
92	GTAIRR1026	346286607
93	GTAIRB1027	346286607
94	RB20073	346286607
95	T0151	346286607
96	T0211	346286607
97	T0221	346286607
98	GTWTCG1111	346286607
99	RB20034B	346286607
100	T0508	346286607
101	GTAIRR1026	346286687
Sheet1

 

[Fluff]

With that sort of data, I wouldn't expect any problems. Try this instead.

Sub SBest()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, nc As Long
   
   With Sheets("Sheet1")
      Ary = .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 300)
   
   For r = 2 To UBound(Ary)
      If Ary(r, 2) <> Ary(r - 1, 2) Then
         nr = nr + 1
         Nary(nr, 1) = Ary(r, 2)
         Nary(nr, 2) = Ary(r, 1)
         nc = 3
      Else
         nc = nc + 1
         Nary(nr, nc) = Ary(r, 1)
      End If
   Next r
   Sheets("Sheet1").Range("D2").Resize(nr, 300).Value = Nary
End Sub