Looking for a way to return unique text values from another sheet.

M.OConnell

I have a sheet that includes a long list of parts and materials, across multiple products, so there are a lot of duplicates. I need to create a list of each individual part, one instance per part, regardless of how many there are on the source list. In Excel I would use this formula - "=SORT(UNIQUE(ALL_PRODUCT_BOM_TABLE[PART NUMBER]))", where "ALL_PRODUCT_BOM_TABLE" is the table I'm pulling from, and "[PART NUMBER]" is the column containing all of the part numbers that I need to sort.

I've seen the Distinct command, but I cannot get that to work, I suspect it is because that needs to be nested in another formula. I also see some people referencing an Index method, but I can't get my head around how that works yet...

Is there a way to do this?

Mary_A

We have something similar but the architecture is a bit reversed. We have a product that are possible in a BOM. Then, we build a BOM from that part list. Any row in a BOM references a part (index), pulls a description/cost whatever, and has a quantity. A part can appear multiple times in a BOM.

The solution you need might driven by the ultimate goal. Do you need a product list that any BOM can pull from? Do you need an ongoing report of unique products in any BOM?

Mary_A

We have something similar but the architecture is a bit reversed. We have a product that are possible in a BOM. Then, we build a BOM from that part list. Any row in a BOM references a part (index), pulls a description/cost whatever, and has a quantity. A part can appear multiple times in a BOM.

The solution you need might driven by the ultimate goal. Do you need a product list that any BOM can pull from? Do you need an ongoing report of unique products in any BOM?

M.OConnell

Thanks! Ultimately what I'm looking to do is give my purchasing department a list of all components that make up the products that are listed on the BOMs, so they can then see what they need to order based on demand and inventory gaps.

But now that I think of it, I already have a list of all materials, with pricing and OEM and such. I might just be able to use that instead...

But Thank You!!