# Rows to Columns

#### Gerrit.B

##### Board Regular
In my Excel sheet I have multiple rows with data from the same customer. (orange)
I need to transpose this data to one row per customer, where all invoice numbers are in 1 line as shown in image (green part)

How can this be done?

#### Attachments

• 2020-07-14_095253.png
10.1 KB · Views: 43

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### farmerscott

##### Well-known Member
Hi,

Do you want a formula solution or a VBA solution?

FS

#### Gerrit.B

##### Board Regular
I need a working solution, but prefer vba.

#### Peter_SSs

##### MrExcel MVP, Moderator
Assuming your data is sorted by customer as per your sample, try this with a copy of your data

VBA Code:
``````Sub Rearrange()
Dim a As Variant, b As Variant
Dim i As Long, r As Long, c As Long, MaxCols As Long

a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a), 1 To 2)
For i = 2 To UBound(a)
If a(i, 1) <> a(i - 1, 1) Then
r = r + 1
b(r, 1) = a(i, 1)
b(r, 2) = a(i, 2)
c = 2
Else
c = c + 1
If c > MaxCols Then
MaxCols = c
ReDim Preserve b(1 To UBound(b), 1 To MaxCols)
End If
b(r, c) = a(i, 2)
End If
Next i
With Range("E1").Resize(, MaxCols)
.Formula = "=""Invoice ""&COLUMNS(\$E1:E1)-1"
.Value = .Value
.Cells(1).ClearContents
.Offset(1).Resize(r).Value = b
.EntireColumn.AutoFit
End With
End Sub``````

BTW ..
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

#### Gerrit.B

##### Board Regular
Thanks Peter,

This works perfect!

Also updated my profile with Office version and platform.

Regards,

Gerrit

#### alansidman

##### Well-known Member
An alternative is to use Power Query/Get and Transform found on the Data Tab. Mcode is as follows.

Rich (BB code):
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Invoice", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Data", each _, type table [Customer=text, Invoice=number]}}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Invoice", "Index"}, {"Custom.Invoice", "Custom.Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Invoice", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Invoice1"}, {"2", "Invoice2"}, {"3", "Invoice3"}})
in
#"Renamed Columns"``````

Book2
ABCD
1CustomerInvoice1Invoice2Invoice3
2Customer155
3customer2565758
4customer359
5customer46061
Sheet2

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

.. and for the profile update.

#### sandy666

##### Banned - Rules violations
@alansidman
 Customer Invoice1 Invoice2 Invoice3 4 5 Customer 1 55 Customer 2 56 57 58 Customer 3 59 Customer 4 60 61 Customer 5 100 120 130 140 150

so maybe
 Customer Invoice.1 Invoice.2 Invoice.3 Invoice.4 Invoice.5 Customer 1 55 Customer 2 56 57 58 Customer 3 59 Customer 4 60 61 Customer 5 100 120 130 140 150

Rich (BB code):
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"Customer"}, {{"Count", each _, type table}}),
List = Table.AddColumn(Group, "Invoice", each [Count][Invoice]),
Extract = Table.TransformColumns(List, {"Invoice", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
SortLen = Table.Sort(Table.AddColumn(Extract, "Length", each Text.Length([Invoice]), Int64.Type),{{"Length", Order.Descending}}),
Split = Table.SplitColumn(SortLen, "Invoice", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv)),
Sort = Table.Sort(Table.RemoveColumns(Split,{"Count", "Length"}),{{"Customer", Order.Ascending}})
in
Sort``````

#### sandy666

##### Banned - Rules violations
@alansidman
Rich (BB code):
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"Data", each _, type table [Customer=text, Invoice=number]}}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Invoice", "Index"}, {"Custom.Invoice", "Custom.Index"}),
#"Added Prefix" = Table.TransformColumns(#"Expanded Custom", {{"Custom.Index", each "Invoice " & Text.From(_, "en-GB"), type text}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Prefix", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Prefix", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Invoice", List.Sum)
in
#"Pivoted Column"``````

#### alansidman

##### Well-known Member
Thanks Sandy. Thought about the Added Prefix step after I posted, but was to lazy to go back and amend it. Nice addition.

Replies
3
Views
58
Replies
1
Views
142
Replies
2
Views
120
Replies
0
Views
63
Replies
7
Views
246

1,181,589
Messages
5,930,770
Members
436,759
Latest member
grnelson1797

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back