Filter a Join Collect formula from another sheet.
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.
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Perfect!! Thank you so much for the help.
-
No problem! I'm glad it works for you 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!