Merge Every 3 Rows into 1 row

S

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.
 
Sort by date Sort by votes
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
123​
[/td][td][/td][td=bgcolor:#E2EFDA]123, 150, 177[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
150​
[/td][td][/td][td]204, 231, 258[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
177​
[/td][td][/td][td=bgcolor:#E2EFDA]285, 312, 350[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
204​
[/td][td][/td][td]345, 340, 335[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
231​
[/td][td][/td][td=bgcolor:#E2EFDA]330, 325, 320[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
258​
[/td][td][/td][td]315, 310, 305[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
285​
[/td][td][/td][td=bgcolor:#E2EFDA]300, 295, 290[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
312​
[/td][td][/td][td]285, 280, 275[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
350​
[/td][td][/td][td=bgcolor:#E2EFDA]270, 265, 260[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
345​
[/td][td][/td][td]255, 250, 245[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
340​
[/td][td][/td][td=bgcolor:#E2EFDA]240[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
335​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
330​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
325​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
320​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
315​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
310​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
305​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
300​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
295​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
290​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
285​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
280​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
275​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
270​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
265​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
260​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
255​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
250​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
245​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
240​
[/td][td][/td][td][/td][/tr]
[/table]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom.1[/td][td=bgcolor:#70AD47]Custom.2[/td][td=bgcolor:#70AD47]Custom.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
123​
[/td][td][/td][td=bgcolor:#E2EFDA]123[/td][td=bgcolor:#E2EFDA]150[/td][td=bgcolor:#E2EFDA]177[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
150​
[/td][td][/td][td]204[/td][td]231[/td][td]258[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
177​
[/td][td][/td][td=bgcolor:#E2EFDA]285[/td][td=bgcolor:#E2EFDA]312[/td][td=bgcolor:#E2EFDA]350[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
204​
[/td][td][/td][td]345[/td][td]340[/td][td]335[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
231​
[/td][td][/td][td=bgcolor:#E2EFDA]330[/td][td=bgcolor:#E2EFDA]325[/td][td=bgcolor:#E2EFDA]320[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
258​
[/td][td][/td][td]315[/td][td]310[/td][td]305[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
285​
[/td][td][/td][td=bgcolor:#E2EFDA]300[/td][td=bgcolor:#E2EFDA]295[/td][td=bgcolor:#E2EFDA]290[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
312​
[/td][td][/td][td]285[/td][td]280[/td][td]275[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
350​
[/td][td][/td][td=bgcolor:#E2EFDA]270[/td][td=bgcolor:#E2EFDA]265[/td][td=bgcolor:#E2EFDA]260[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
345​
[/td][td][/td][td]255[/td][td]250[/td][td]245[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
340​
[/td][td][/td][td=bgcolor:#E2EFDA]240[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
335​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
330​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
325​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
320​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
315​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
310​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
305​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
300​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
295​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
290​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
285​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
280​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
275​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
270​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
265​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
260​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
255​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
250​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
245​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
240​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
644
Raddle
R
J
  • Question Question
Any advice on Append / Merge Power query using VBA
Replies
0
Views
1K
juzzie
J
Q
  • Question Question
Automatically insert new rows with predefined values
Replies
1
Views
737
quattroformatee
Q
J
  • Question Question
Create a single list of records
Replies
3
Views
223
Peter_SSs
Peter_SSs
I
  • Solved
Fast VBA to Post 1000's of data points that show cell value ("Y") in another worksheet
Replies
3
Views
636
offthelip
O

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

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