Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Need Help with Excel Spreadsheet

Copper Contributor

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:

1Aaron JohnsonB20-674A
B20-674Aa
B21-281A
2Aaron NormanB21-320A
3Adam ScannellB20-608R
B20-608Rr
4Addie ShaferB20-715Bb
5Alex BergeronB22-014Bb
6Alex BridgesB22-455A
B22-455Aa
B22-455B
B22-455Bb
7Amy PaddockB21-114A
B21-114Aa
B21-114B
B21-114Bb
B21-114Cc
8Amy SkuzaB20-163A
B20-163B
B22-449B

 

Thank you!
 

12 Replies

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

job number.JPG

@BusterCLARK 

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

image.png

multiline text or we have 3 rows with texts?

Version 2211.
Multiline text by typing the job# and hold ALT key and press enter.
Thank you. Is there a way you can convert the Query Text to English just in case I have to edit something please.

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

applied steps.JPG

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

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

 

Screenshot_2023-01-05-10-34-16-137_cn.uujian.browser.jpg

@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

 

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

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

 

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

@L z. 

Perhaps I'm wrong, will see