SOLVED

VBA to Concatenate rows based on another cells value

Copper Contributor

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.

 

9 Replies

Hi @leonpeachey 

 

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

Sample.png

best response confirmed by leonpeachey (Copper Contributor)
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

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?

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

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

Just realised I can simplify the formula using SINGLE

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

image.png

@Peter Bartholomew 

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?

BTW @Peter Bartholomew 

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

@L z. 

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

1 best response

Accepted Solutions
best response confirmed by leonpeachey (Copper Contributor)
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

View solution in original post