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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,457
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,675
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
921
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,675
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,457
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,457
Glad we could be of help and thank you for letting us know that all is to your wishes.
Good Luck.
 

Forum statistics

Threads
1,089,491
Messages
5,408,583
Members
403,216
Latest member
Boba Fetts

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top