Determine Available Inventory - Several Variables

[SeveredLegoHead]

Hello,

I am in need of assistance. I'm working on a spreadsheet to simplify my work life and have run into a wall. At a high level, the purpose of this spreadsheet is to determine the items & inventory available for me to send to a third party weekly. I'm going to try my best to put everything into a way that makes sense here, but I've been working on it so long that it's all starting to swirl in my head, so please, if anything seems wrong or doesn't make sense, let me know!

My vendor sends me what they have available once a week, which I load into a new tab. My available inventory is supplied once a week, which I update on an "Inventory Counts" tab.

I have a tab dedicated to just finding the available inventory, however, this is where variable 1 comes in. My inventory is supplied on a component level, but many of my vendor's items are combos. Each combo only has 1 item #, but may have several components. The largest combo currently available has 7 components. But I've built out room for 10 components in case that changes in the future.

On this tab, I have the following columns: Vendor SKU, My SKU, Available Inventory, Item Description, Component 1(if only 1 component, this is the same as My SKU), Component 2, Component 3, Component 4, Component 5, Component 6, Component 7, Component 8, Component 9, Component 10, Available Inventory for Component 1, Available Inventory for Component 2, Available Inventory for Component 3, Available Inventory for Component 4, Available Inventory for Component 5, Available Inventory for Component 6, Available Inventory for Component 7, Available Inventory for Component 8, Available Inventory for Component 9, Available Inventory for Component 10.

Vendor SKU, My SKU, Item Description, & the 10 Components are static.
The 10 Available Inventory for Components are VLOOKUPS into the separate Inventory Counts tab.
Available Inventory is the column I'm trying to return the correct value in.​

All of this so far, I think I've managed to work through. Currently, the formula in Available Inventory is "=MINIFS(Q2:Z2,Q2:Z2,">" & 0)", where Q2:Z2 are the 10 Available Inventory for Component columns. But, this is where variable 2 comes in and stumps me. What none of this accommodates for is what if 2 of the components are the same item within a combo? Which happens quite often in my combos. So the available inventory for a component will need to be halved if there's 2 of a component, or put into thirds if there's 3 of a component, etc. But this is where I need help. I have to believe there's a way to do this, but I suspect it's not going to be as easy as just a more elaborate formula in the Available Inventory column.

Thanks again for any help everyone is able to provide!

 

[Rijnsent]

Hi SLH,
that almost sounds like a database challenge. Having said that, can you maybe put a file in a dropbox/onedrive/etc. for us to have a look at? It could be part mock-up, but to help you I'd need to see a bit more of what you've created so far.
Thanks,
Koen