Next possible match preventing duplicates

Options

I am trying to run a reconciliation of parts numbers, when there are multiple line items of the same part number only the first match is returned. How do I write the formula to move to the next possible match?

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    Hello @Ashley Gentle,

    Do you want your database to contain all of the duplicates, or would you prefer to have the duplicates removed?

    Also, this post might help

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Ashley Gentle
    Options

    Hi Dan,

    Yes, we are wanting all of the duplicates to each have their own line. In any one project we can have multiple items with the same part number but different serial numbers, we want a return that reflects each one documented to perform a reconciliation.

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 07/09/24
    Options

    So you want to have a list of serial numbers for any given part number, for each project? For that use JOIN and COLLECT

    = JOIN(COLLECT([Serial Numbers]:[Serial Numbers], [Part Number]:[Part Number], [Part Number]@row), ",")

    For each row's Part Number this formula will give you a comma separated list of all the Serial Numbers it finds in the sheet that have the same Part Number next to them.

    If the Serial Numbers are on a different sheet, replace [Serial numbers]:[Serial Numbers] with a cross-sheet reference to the Serial Numbers column on the second sheet by clicking the "Reference Another Sheet" link while entering the formula.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Ashley Gentle
    Options

    Brian,

    That formula works great however it is still only returning the first serial number it finds, I would like the formula to go to the next available serial number after one is already returned.

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Options

    Perhaps I’m misunderstanding what you’re trying to do. Can you post a screenshot of the serial number and product number data sheet?

    The formula I provided will get all the serial numbers from the sheet that match the product number.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Ashley Gentle
    Options

    Correct, the formula did pull the serial numbers to match the product number. I misspoke, it did return all of the serial numbers however they are all combined. In the screenshot above for example part number 0132ADT has 3 separate rows, each of those are showing all of the serial numbers for that part number. What I am looking for is one serial number per line, not duplicating as it goes to the next.

    The below is snip is the sheet I am pulling the information from to match with the original part number.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!