Forum Discussion

leonpeachey's avatar
leonpeachey
Copper Contributor
May 15, 2024
Solved

VBA to Concatenate rows based on another cells value

Hi,   I have a table of data from a system I use. some rows contain jobs and rows directly below each job contain the separate tasks that need to be completed (including the name of the person dele...
excel
Macros and VBA
  • leonpeachey's avatar
    leonpeachey
    May 24, 2024

    Hi all,

     

    I found what I need that does what I need. It doesn't remove duplicates if the same person has multiple tasks but that's not a major thing.

     

    Dim s As String
Dim i As Integer
For i=1 To 1000

Start:
If Cells(i, 6) <> "" And Cells(i + 1, 6) = "" And Cells(i + 7, 7)<> "" Then
s = Cells(i, 7).Value & CH(10) & Cells9I +1, 7).Value
Cells(i,7) = s
Cells(i + 6).EntirRow.Delete
GoTo start
End If
Next

     

    Now I one Row for the Job, and in one of the cell I have the name of each person assigned a task. Instead of being separated by a comma, I opted to have each name on a separate line break.

     

    Thanks for everyone who gave me ideas and help.

    Cheers

  • leonpeachey's avatar
    leonpeachey
    Copper Contributor
    May 24, 2024
    Not sure this will work in my situation due to constraints at work, how the files are store and the my team's excel experience. But that doesn't mean it wont 🙂 What transform commands did you use to achieve your example?
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 24, 2024

      Hi leonpeachey 

       

      Thanks for sharing your solution 🙂

       

      If you're not a bit familiar with Power Query you'll have a challenge. To follow the query steps:

      • Go to Excel Data tab > Queries & Connection
      • Double-click on SummarizedData in the Queries & Connection pane (Power Query Editor will open)
      • Click each step in the APPLIED STEPS "window" (on the right side of the screen)

      Corresponding query code:

      let
    Source = Excel.CurrentWorkbook(){[Name="TableData"]}[Content],
    GroupedJobs = Table.Group(Source, {"Job"},
        {
            {"DATA", each Table.FirstN( Table.RemoveColumns(_, {"Delegate"}), 1), type table},
            {"Delegates", each Text.Combine([Delegate], "; ")}
        }
    ),
    ExpandedExpectedFields = Table.ExpandTableColumn(GroupedJobs, "DATA", {"Task", "Due Date"})
in
    ExpandedExpectedFields

       

      Power Query doc. is available https://learn.microsoft.com/en-us/powerquery-m/

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        May 24, 2024

        Lorenzo I am impressed.  My level of use of PowerQuery is mainly built through the the GUI with rare excursions into the advanced formula editor to make minor adjustments such as renaming fields.  Your formula worked well but would not come naturally to me; there are so many Table functions to know!

         

        I tried the new GROUPBY function but

        = DROP(GROUPBY(Job, HSTACK(Title,Date,Person), HSTACK(TOP, MAX, JOIN),,0),1)

"where"

TOP
= LAMBDA(array, CONCAT(TAKE(array,1,1)));

JOIN
=LAMBDA(text, TEXTJOIN(",",,text));

        was not that straightforward either.

Resources