Forum Discussion

BusterCLARK's avatar
BusterCLARK
Copper Contributor
Jan 04, 2023

Need Help with Excel Spreadsheet

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 - B1...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 04, 2023

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's avatar
BusterCLARK
Copper Contributor
Jan 04, 2023
Version 2211.
Multiline text by typing the job# and hold ALT key and press enter.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 04, 2023

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

Resources