Excel : Copy multiple columns to rows based on one column

[raviansal]

I'm looking for the most efficient approach to transferring information from three columns to one row, right next to each other depending on the company name. Consider the following example:

1. I have a 226k data list with product details for several customers (around 30-40k).
2. One customer can have 2 to 50 products, each with a serial number and a product number. Check out the source data pic.

Output required: One row per customer will have all the product data, serial number, and product number next to each other.

Source data

Product name	Serial number	Product number	Customer Name
yangoo​	12345​	55​	Panasonic​
iwg​	234567​	jhx​	Panasonic​
wzfgsxh​	uwgzd​	234567​	Panasonic​
zqwfdxutqfx​	234567​	hgv​	Panasonic​
awertz​	hwdchg​	23456​	Panasonic​
yangoo​	qixgqz​	22​	Apple​
iwg​	1234567​	3456​	Apple​
wzfgsxh​	2321​	7u47545​	Apple​
zqwfdxutqfx​	823828-33​	44​	Apple​
awertz​	4378-34​	432​	Apple​
yangoo​	84746-1i6​	214​	Apple​
iwg​	U18H3032​	124321​	Apple​
wzfgsxh​	276876​	1324​	Apple​

Desired output:

 

[JGordon11]

In Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Customer Name"}, {{"All", each List.Combine(Table.ToRows(Table.RemoveColumns(_, {"Customer Name"})))}}),
    tbl = Table.AddColumn(Group, "Count", each List.Count([All])/3),
    max = List.Max(tbl[Count]),
    tcn = List.Accumulate({1..max}, {"Customer Name"}, (s,c)=> let n = Number.ToText(c) in s & {"Product name " & n, "Serial number " & n, "Product number " & n}),
    tbl1 = Table.AddColumn(tbl, "List", each {[Customer Name]} & [All] & List.Repeat({null,null,null}, max - [Count])),
    Result = Table.FromRows(tbl1[List], tcn)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Product name	Serial number	Product number	Customer Name		Customer Name	Product name 1	Serial number 1	Product number 1	Product name 2	Serial number 2	Product number 2	Product name 3	Serial number 3	Product number 3	Product name 4	Serial number 4	Product number 4	Product name 5	Serial number 5	Product number 5	Product name 6	Serial number 6	Product number 6	Product name 7	Serial number 7	Product number 7	Product name 8	Serial number 8	Product number 8
2	yangoo	12345	55	Panasonic		Panasonic	yangoo	12345	55	iwg	234567	jhx	wzfgsxh	uwgzd	234567	zqwfdxutqfx	234567	hgv	awertz	hwdchg	23456
3	iwg	234567	jhx	Panasonic		Apple	yangoo	qixgqz	22	iwg	1234567	3456	wzfgsxh	2321	7u47545	zqwfdxutqfx	823828-33	44	awertz	4378-34	432	yangoo	84746-1i6	214	iwg	U18H3032	124321	wzfgsxh	276876	1324
4	wzfgsxh	uwgzd	234567	Panasonic
5	zqwfdxutqfx	234567	hgv	Panasonic
6	awertz	hwdchg	23456	Panasonic
7	yangoo	qixgqz	22	Apple
8	iwg	1234567	3456	Apple
9	wzfgsxh	2321	7u47545	Apple
10	zqwfdxutqfx	823828-33	44	Apple
11	awertz	4378-34	432	Apple
12	yangoo	84746-1i6	214	Apple
13	iwg	U18H3032	124321	Apple
14	wzfgsxh	276876	1324	Apple
15
Sheet1

 

[Peter_SSs]

Welcome to the MrExcel board!

Power Query may well be your best bet but if you want to try a macro approach, you could try this.
I have assumed that the raw data is grouped/sorted by Customer Name like you sample data.

Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, ProdCount As Long, uba As Long, x As Long, y As Long
  Dim Cust As String
 
  a = Range("A1", Range("D" & Rows.Count).End(xlUp).Offset(1)).Value
  uba = UBound(a)
  ReDim b(1 To uba, 1 To 1)
  b(1, 1) = "Customer Name"
  i = 2
  k = 1
  Do
    k = k + 1
    Cust = a(i, 4)
    j = 0
    y = 1
    b(k, 1) = a(i, 4)
    Do While a(i + j, 4) = Cust And i < UBound(a)
      j = j + 1
      If j > ProdCount Then
        ProdCount = ProdCount + 1
        ReDim Preserve b(1 To uba, 1 To ProdCount * 3 + 1)
        b(1, ProdCount * 3 - 1) = "Product name " & ProdCount
        b(1, ProdCount * 3) = "Serial number " & ProdCount
        b(1, ProdCount * 3 + 1) = "Product number " & ProdCount
      End If
      For x = 1 To 3
        y = y + 1
        b(k, y) = a(i + j - 1, x)
      Next x
    Loop
    i = i + j
  Loop Until i >= uba
  With Range("F1").Resize(k, UBound(b, 2))
    .Value = b
    .Columns.AutoFit
  End With
End Sub

My sample data and results:

raviansal.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Product name	Serial number	Product number	Customer Name		Customer Name	Product name 1	Serial number 1	Product number 1	Product name 2	Serial number 2	Product number 2	Product name 3	Serial number 3	Product number 3	Product name 4	Serial number 4	Product number 4	Product name 5	Serial number 5	Product number 5	Product name 6	Serial number 6	Product number 6	Product name 7	Serial number 7	Product number 7	Product name 8	Serial number 8	Product number 8
2	yangoo	12345	55	Panasonic		Panasonic	yangoo	12345	55	iwg	234567	jhx	wzfgsxh	uwgzd	234567	zqwfdxutqfx	234567	hgv	awertz	hwdchg	23456
3	iwg	234567	jhx	Panasonic		Apple	yangoo	qixgqz	22	iwg	1234567	3456	wzfgsxh	2321	7u47545	zqwfdxutqfx	823828-33	44	awertz	4378-34	432	yangoo	84746-1i6	214	iwg	U18H3032	124321	wzfgsxh	276876	1324
4	wzfgsxh	uwgzd	234567	Panasonic
5	zqwfdxutqfx	234567	hgv	Panasonic
6	awertz	hwdchg	23456	Panasonic
7	yangoo	qixgqz	22	Apple
8	iwg	1234567	3456	Apple
9	wzfgsxh	2321	7u47545	Apple
10	zqwfdxutqfx	823828-33	44	Apple
11	awertz	4378-34	432	Apple
12	yangoo	84746-1i6	214	Apple
13	iwg	U18H3032	124321	Apple
14	wzfgsxh	276876	1324	Apple
Sheet1

 

[raviansal]

You guys are the best and saved my whole weekend.

 

[Peter_SSs]

Glad we could help. Thanks for the follow-up.