Merge Every 3 Rows into 1 row

sduttexcel

New Member
Joined
Mar 16, 2018
Messages
22
I have a data file of say 60,000 records and each record are up to column K. I need to combine every 3 rows as one row into a new worksheet.

Input rows will be: A2 to K2, A3 to K3 and A4 to K4

Output will be on a new worksheet will be from:
A2 to AG2

This formula will be for entire worksheet.

There for the quantity in the output file will be 20,000 records.
 
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!!!!
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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:
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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?
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
Glad we could be of help and thank you for letting us know that all is to your wishes.
Good Luck.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top