Filter a Join Collect formula from another sheet.

Chris Benskin

I have sheet, "Component Inventory", that I'm using to summarize the quantity in stock of a part number in a sheet called, "Inventory Log". "Component Inventory" has a QTY ON HAND column that is populated by a SUMIF formula getting its data from "Inventory Log" sheet.

The "Inventory Log" sheet has a column INVENTORY LOCATION that shows where the quantity of a single lot of a part number is. When the inventory of that lot is depleted the field gets changed from it's storage location to "Depleted".

I want a column showing the inventory location of any part we have in stock. This could be multiple storage locations. I have used a JOIN/COLLECT formula to grab the inventory location of a given part number. I'm using this formula to get the inventory locations pulled in, =JOIN(COLLECT({Inventory Receiving Log_FM0049 Rev. B Range 3}, {Inventory Receiving Log_FM0049 Rev. B Range 1}, [MIS P/N]@row), ", ")

The formula does what I want with one exception. I don't want any part that has a quantity <= zero to have its inventory location shown. In other words, I don't want "Deleted" showing up in the INEVNTORY LOCATION column. How do I get the inventory location to show only locations of parts that have a quantity greater than zero?

Thank you in advance for your help.

Genevieve P.

Hi @Chris Benskin

You can add another criteria into your COLLECT function by re-stating the range and saying that it is not (or <>) "Depleted". This will filter out that value and only return the other ones.

Try this:

=JOIN(COLLECT({Inventory Receiving Log_FM0049 Rev. B Range 3}, {Inventory Receiving Log_FM0049 Rev. B Range 1}, [MIS P/N]@row, {Inventory Receiving Log_FM0049 Rev. B Range 3}, <> "Depleted"), ", ")

Let me know if this works for you!

Cheers,

Genevieve