Forum Discussion

leonpeachey's avatar
leonpeachey
Copper Contributor
May 15, 2024

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 here

  • 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.

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

      Just realised I can simplify the formula using SINGLE

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

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        May 25, 2024

        BTW PeterBartholomew1 

        I'm pretty sure I saw you posting an Unpivot lambda some time ago. Unfortunately I can't find it out 😞

        If you still have it somewhere could you re-share it please? + Thanks

Resources