# Merge Every 3 Rows into 1 row

#### sduttexcel

##### New Member
This worked fine. Only problem is, it divides every thing by 3. I have file 45304. If you divide by three its 15,101.3333. There for the last row will be 1 record by it self. Can you modify the script so it captures all records please. Thanks for the help!!!!

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### jolivanes

##### Well-known Member
Re: Only problem is, it divides every thing by 3
What do you mean by that?
Is the result not what you asked for?

Re: Can you modify the script so it captures all records please.
Which records are missing?

Last edited:

#### sduttexcel

##### New Member
What I meant was, if I have 16 records it will be

Records Records Records
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15
16

It ends up at record 15. How can I can get record 16? I tried to modify the script but it did not work.

#### sandy666

##### Well-known Member
because I didn't see any example from you, here is example from me using PowerQuery:

 Column1 Custom 123​ 123, 150, 177 150​ 204, 231, 258 177​ 285, 312, 350 204​ 345, 340, 335 231​ 330, 325, 320 258​ 315, 310, 305 285​ 300, 295, 290 312​ 285, 280, 275 350​ 270, 265, 260 345​ 255, 250, 245 340​ 240 335​ 330​ 325​ 320​ 315​ 310​ 305​ 300​ 295​ 290​ 285​ 280​ 275​ 270​ 265​ 260​ 255​ 250​ 245​ 240​

Code:
[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 0, 1),
Divide3 = Table.TransformColumns(Index, {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
Group = Table.Group(Divide3, {"Index"}, {{"Count", each _, type table}}),
List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column1")),
Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
ROC = Table.SelectColumns(Extract,{"Custom"})
in
ROC[/SIZE]

#### kweaver

##### Well-known Member
Sduttexcel: Your original post indicated that the data ranges from A2:K2 in row 2 and then in row 3 it ranged from A3:K3, etc.
Is this NOT the case now?

#### sandy666

##### Well-known Member
or

 Column1 Custom.1 Custom.2 Custom.3 123​ 123 150 177 150​ 204 231 258 177​ 285 312 350 204​ 345 340 335 231​ 330 325 320 258​ 315 310 305 285​ 300 295 290 312​ 285 280 275 350​ 270 265 260 345​ 255 250 245 340​ 240 335​ 330​ 325​ 320​ 315​ 310​ 305​ 300​ 295​ 290​ 285​ 280​ 275​ 270​ 265​ 260​ 255​ 250​ 245​ 240​

Code:
[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 0, 1),
Divide3 = Table.TransformColumns(Index, {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
Group = Table.Group(Divide3, {"Index"}, {{"Count", each _, type table}}),
List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column1")),
Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
ROC = Table.SelectColumns(Split,{"Custom.1", "Custom.2", "Custom.3"})
in
ROC[/SIZE]

#### jolivanes

##### Well-known Member
Oh, I see.
Code:
For j = 2 To WorksheetFunction.RoundUp(Cells(Rows.Count, 1).End(xlUp).Row / 3, 0)
so it becomes
Code:
For j = 2 To WorksheetFunction.RoundUp(Cells(Rows.Count, 1).End(xlUp).Row / 3, 0) + 1

#### sduttexcel

##### New Member
This worked perfectly.

Thanks for the help!!!!!

#### jolivanes

##### Well-known Member
Glad we could be of help and thank you for letting us know that all is to your wishes.
Good Luck.