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 - 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!
 

Excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 04, 2023

    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.

    • BusterCLARK's avatar
      BusterCLARK
      Copper Contributor
      Jan 04, 2023
      Thank you. Is there a way you can convert the Query Text to English just in case I have to edit something please.
    • 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
        MVP
        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 "-". 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 05, 2023

    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

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jan 05, 2023

      Lorenzo 

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

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jan 05, 2023

        SergeiBaklan 

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

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 05, 2023

    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)

     

Resources