Need Help with Excel Spreadsheet

[BusterCLARK]

I am trying to find client names (Column B) that are associated with the same jobs numbers that are column C and paste all the names in a new column.

For Example:

Jon Doe - B20-999A

Jim Doe - B19-521B

Tim Low - B20-999A

I would like to add "Jon Doe and Tim Low" in one cell because they have the same job number.

Small portion of my spreadsheet:

1	Aaron Johnson	B20-674A B20-674Aa B21-281A
2	Aaron Norman	B21-320A
3	Adam Scannell	B20-608R B20-608Rr
4	Addie Shafer	B20-715Bb
5	Alex Bergeron	B22-014Bb
6	Alex Bridges	B22-455A B22-455Aa B22-455B B22-455Bb
7	Amy Paddock	B21-114A B21-114Aa B21-114B B21-114Bb B21-114Cc
8	Amy Skuza	B20-163A B20-163B B22-449B

Thank you!
 

[#2]

@BusterCLARK 

A solution could be Power Query. In the attached file you can enter data in the dynamic table in columns A and B. Then you can click in any cell of the green table and right-click with the mouse. Then you can select refresh to update the query.

[#3]

@BusterCLARK 

Could you please clarify on which Excel version/platform you are and is here

multiline text or we have 3 rows with texts?

[#4]

Version 2211.
Multiline text by typing the job# and hold ALT key and press enter.

[#5]

Thank you. Is there a way you can convert the Query Text to English just in case I have to edit something please.

[#6]

@BusterCLARK 

You are welcome. I've translated the query steps into english. Everything else should translate into your language when you open the Power Query Editor.

[#7]

@BusterCLARK 
Thank you. Perhaps that's 365 Current Channel. When this could work

getJobs = LAMBDA(str,
    UNIQUE(
        TEXTBEFORE(
            TEXTSPLIT(TEXTJOIN(CHAR(10), , str), , CHAR(10)),
            "-"
        )
    )
);

isJobHere = LAMBDA(job, str, SUM(--(getJobs(str) = job)));

jobNames = LAMBDA(job, tbl,
    TEXTJOIN(
        ", ",
        ,
        BYROW(
            tbl,
            LAMBDA(rw,
                IF(
                    isJobHere(job, INDEX(rw, 1, 2)),
                    INDEX(rw, 1, 1),
                    ""
                )
            )
        )
    )
);

assignedOnJob = LAMBDA(tbl,
    LET(
        jobs, getJobs(CHOOSECOLS(tbl, 2)),
        names, BYROW(jobs, LAMBDA(rw, jobNames(rw, tbl))),
        VSTACK({"Job", "Names"}, HSTACK(jobs, names))
    )
);

assuming job is text before "-". 

[#8]

create temp table aa as
select Column1,udf_trim(Column2,'"') Column2 from split_data_textjoin_by_column2;
cli_split_data~aa~\s~Column2;
select Column2,group_concat(Column1) from aasplit group by Column2; 

http://e.anyoupin.cn/ceshi/jstest/pull_up_demo.php?s=split_data_text

[#9]

@BusterCLARK 

Another Power Query option (attached)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitByLineFeed = Table.ExpandListColumn(
        Table.TransformColumns(Source,
            {"Job", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv),
                let
                    itemType = (type nullable text) meta [Serialized.Text = true]
                in
                    type {itemType}
            }
        ),
        "Job"
    ),
    GroupedJobCount = Table.Group(SplitByLineFeed, {"Job"},
        {"JOB_COUNT", each Table.RowCount(_), Int64.Type}
    ),
    FilteredJobCount = Table.SelectRows(GroupedJobCount, each [JOB_COUNT] > 1),
    RemovedJobCount = Table.RemoveColumns(FilteredJobCount,{"JOB_COUNT"}),
    MergedTables = Table.NestedJoin(
        RemovedJobCount, {"Job"},
        SplitByLineFeed, {"Job"},
        "COMMON", JoinKind.Inner
    ),
    ConcatClients = Table.AddColumn(MergedTables, "Clients", each
        Text.Combine([COMMON][Client], ", "), type text
    ),
    RemovedColumn = Table.RemoveColumns(ConcatClients,{"COMMON"})
in
    RemovedColumn

[#10]

@L z. 

Again, I'm not sure the job is like "B20-674A", not "B20". Based on the sample within the text of initial post.

[#11]

@BusterCLARK 

Here's my entry:

'Header to be applied
Header
={"ID", "Name"}

'Dynamic range for ID column
IDCol
=LET(c, Sheet2!$C$1:$C$10000, nonblank, COUNTA(c), TAKE(c, nonblank))

'Dynamic range for Name Column
NameCol
=LET(b, Sheet2!$B$1:$B$10000, nonblank, COUNTA(b), TAKE(b, nonblank))

'Split IDs on carriage return and stack.
IDStack(a, v)
=LET(split, TEXTSPLIT(v, , CHAR(10)), VSTACK(a, split))

'Create a new ID column
NewIDCol
=DROP(REDUCE("", IDCol, IDStack), 1)

'Resize Name Column based on how many carriage returns are in ID Col for a given name.
NStack(a, v)
=VSTACK(a, EXPAND(v, ROWS(TEXTSPLIT(XLOOKUP(v, NameCol, IDCol, ""), , CHAR(10))), , v))

'Put together a new Name Column
NewNameCol
=DROP(REDUCE("", NameCol, NStack), 1)

'The final stack.
FStack(a, v)
=LET(f, FILTER(NewNameCol, NewIDCol = v), VSTACK(a, HSTACK(v, TEXTJOIN(", ", , f))))

'sheet level formula
=REDUCE(Header,UNIQUE(NewIDCol),FStack)

[#12]

@Sergei Baklan 

I'll be glad to fix my mistake but I don't see where it is, or I misunderstood what's expected

Let's see what @BusterCLARK has to say...

[#13]

@L z. 

Perhaps I'm wrong, will see