VBA to Concatenate rows based on another cells value

[leonpeachey]

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.

Job1	Title1	Due Date
Job1	Task1		John
Job1	Task2		John
Job1	Task3		Mike
Job2	Title2	Due Date
Job2	Task1		Brad
Job2	task2		Mike
Job3	Title 3	Due Date

converted to

Job1	Title1	Due Date	John John
Job2	Title2	Due Date	Brad Mike
job3	Title3	Due 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.

[#2]

Hi @leonpeachey 

If this can help you... Easy to do with Power Query:

[#3 · Accepted solution]

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

[#4]

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?

[#5]

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

[#6]

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

[#7]

Just realised I can simplify the formula using SINGLE

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

[#8]

@PeterBartholomew1 

Not being insider I don't have PIVOTBY, GROUPBY...yet but understand what they do (+ the eta lambdas)

Though I'm confused with your ref. to SINGLE. Except if I missed something on the various Excel blogs I haven't seen an upcoming SINGLE function. So, is it one your famous 🙂 Lambdas?

[#9]

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

[#10]

@Lorenzo 

[Seems I failed to post this reply!]

Firstly, I have attached a copy of my hitherto unsaved file with SINGLE for future reference.  The SINGLE function was announced with the introduction of dynamic arrays in 2018 but was quickly replaced by the more concise '@' notation.  Used with a range reference it returns a relative reference to a cell on the same row or column but with an array formula it returns the first array element as a scalar.  Normally Excel automatically replaces SINGLE with '@' but here, used as an eta reduced Lambda, the function retains its form.

As for the 'unpivot' file, this is the sort of thing where there is no guaranty that I have done it right but there is every expectation that, at some time, I will find myself doing it again!  Nevertheless, I have turned up a recent workbook that may meet the spec.