Need Help with Excel Spreadsheet

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)

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

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

BusterCLARK 

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

multiline text or we have 3 rows with texts?

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.