Best matches

[sentinela]

Hello everyone,
I am on a spread sheet to bid changing doors in a few houses. Yet, at the same time, I am trying to use some doors that I have in stock.

I want to make it work where I can use the most of my doors.

For example, this is my stock...

45 30" 6-panel
30 24" 6-panel
29 30" 2-panel
46 24" 2-panel

The houses that I will use break down on something similar to this...

House A 5 - 30" and 4 - 24"
House B 3 - 30" and 6 - 24"

And it goes on another 5 houses, all with different numbers.
I can work out where I can do this manually and figure out doing it one by one.

It doesn't matter what style I use in what house. My goal is to use the most of my inventory.
Does anyone have any ideas how to work this out in the spreadsheet?

Thanks in advance.

Sentinela

[teylyn]

Hi Sentinela,

try the attached file as a starting point. I have created two sheets, one for the list of your doors in stock and one for calculating the requirements.

On the Calculations tab, the doors are summed up with a SUMIF statement depending on their width, so all 30" doors get summed and all 24" get summed, regardless of their panel numbers. The way the stock list is layed out, you can adjust the spreadsheet later to take the panels into consideration, if you wish.

Below the summary of the doors in stock is a list of the houses, with a column for each door type. You enter the number of 30" and 24" doors required for each house.

These are summed up and subtracted from the doors in stock. Below that you'll find a row with information on how many doors (if any) of each kind you need to order and below that how many doors (if any) remain in stock after the job has closed.

Of course, whenever you take doors out of your storage, you have to update the stock list.

hope that helps

[sentinela]

Hello Teylyn,

Thanks for your help.
I see what you are saying here, however, the only problem is I cannot mix 6-panel and 2-panels in the same house.
I am attaching the beginnings of my works. On row#2 the cells that are highlighted in blue, are the ones that I can just say how many houses in that style I am working on and then I can see how doors I actually need.
Now, this is my main problem... Which doors can I utilize best; in the sense of having less in stock after all.
I do not have the stock in that sheet, but I actually have 5 different styles.

Thanks again.

[teylyn]

Well, you have it all there. You just need to compare the stock with the number of doors required and then subtract one from the other. Add a few columns to the right of your table:

- a column that lists number of doors in stock for each door type
- a column that calculates the difference between stock and requirements

I'd personally prefer the tables to be flipped around, so I have more rows than columns. I find it easier to scroll down than to scroll right.

See the sheet 2 of the attached file for a suggestion.

Is that what you need? I'm wondering, since your spreadsheet does not mention any panels at all, just sizes

hth