Next possible match preventing duplicates
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

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!

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.

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 crosssheet reference to the Serial Numbers column on the second sheet by clicking the "Reference Another Sheet" link while entering the formula.

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.

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.

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
Categories
Check out the Formula Handbook template!