Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Merge Every 3 Rows into 1 row
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Mar 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Every 3 Rows into 1 row

    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!!!!

  2. #12
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,377
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Every 3 Rows into 1 row

    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 by jolivanes; Aug 3rd, 2019 at 02:29 AM. Reason: spelling mistake fixed
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  3. #13
    New Member
    Join Date
    Mar 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Every 3 Rows into 1 row

    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.

  4. #14
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,846
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Merge Every 3 Rows into 1 row

    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:
    // 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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  5. #15
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Every 3 Rows into 1 row

    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?

  6. #16
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,846
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Merge Every 3 Rows into 1 row

    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:
    // 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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  7. #17
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,377
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Every 3 Rows into 1 row

    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
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  8. #18
    New Member
    Join Date
    Mar 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Every 3 Rows into 1 row

    This worked perfectly.

    Thanks for the help!!!!!

  9. #19
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,377
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Every 3 Rows into 1 row

    Glad we could be of help and thank you for letting us know that all is to your wishes.
    Good Luck.
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •