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 delegated for that task)

I'm trying to use VBA to modify the table to show the Job and a cell with the delegates, separated by a comma or line break.

e.g.

Job1Title1Due Date 
Job1Task1 John
Job1Task2 John
Job1Task3 Mike
Job2Title2Due Date 
Job2Task1 Brad
Job2task2 Mike
Job3Title 3Due Date 

 

converted to

Job1Title1Due Date

John

John

Job2Title2Due Date

Brad

Mike

job3Title3Due Date 

 

The actual table has over 200 jobs and most have several tasks each, and I have other VBA elements that modifies other columns.  I can do the basic VBA for the rest of the table, but this has me stumped.

 

Can anyone point me in the right direction please.

Leon.

 

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

  • leonpeachey's avatar
    leonpeachey
    Copper Contributor
    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

Resources