Merge Every 3 Rows into 1 row

sduttexcel

New Member
Joined
Mar 16, 2018
Messages
19
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!!!!
 

Some videos you may like

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
Joined
Sep 5, 2004
Messages
1,459
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
Joined
Mar 16, 2018
Messages
19
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
Joined
Oct 24, 2015
Messages
4,701
because I didn't see any example from you, here is example from me using PowerQuery:

Column1Custom
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
Joined
May 8, 2018
Messages
928
Office Version
365, 2010
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
Joined
Oct 24, 2015
Messages
4,701
or

Column1Custom.1Custom.2Custom.3
123​
123150177
150​
204231258
177​
285312350
204​
345340335
231​
330325320
258​
315310305
285​
300295290
312​
285280275
350​
270265260
345​
255250245
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
Joined
Sep 5, 2004
Messages
1,459
Oh, I see.
Add +1 to this line
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
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,459
Glad we could be of help and thank you for letting us know that all is to your wishes.
Good Luck.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,886
Messages
5,410,973
Members
403,336
Latest member
amreeves87

This Week's Hot Topics

Top