Next possible match preventing duplicates

Ashley Gentle

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?

SoS | Dan Palenchar

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

https://community.smartsheet.com/discussion/87161/lookup-with-duplicate-values

Ashley Gentle

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

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.

Ashley Gentle

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

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.

Ashley Gentle

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.