Share via

Create Shopping List from Inventory That automatically isolates out of stock

Anonymous
2021-05-11T21:43:32+00:00

This is only a private project, creating an inventory of home grocery items which I can mark when in stock or out of stock. For my fortnightly shopping.

Is there a way to create a function that puts items into the correct order so that items out of stock appear in a group? For example in the perfect world I would have two work sheets. One with the inventory where I have the full list (Vegeatables, Fridge, Freezer, Laundry, Bathroom, etc. In that work sheet I keep the records of what I have or not and in the other sheet it automatically lists those items out of stock.. That way I can print out, or have on my phone the shopping list of only the items I need.

Below is a sample of my invetory list. with a simple function  of =sum(B1-C1) in Column D.

In my ideal outcome on the next work sheet only the items with a value of 1 or above would appear, so if I choose to printout the shopping list it is only of the ones that are out of stock.

Cheers, Mike

Inventory List
Vegetables In Out
Potatoes 1 0 1
Onions 1 1 0
Broccolli 2 1 1
Capsicum 1 1 0
Mushroom 1 0 1
Kumara 1 0 1
Cabbage 1 1 0
Carrots 1 0 1
Cucumber 2 0 2
Silverbeet 1 0 1
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2021-05-12T00:18:46+00:00

    Hi Mike

    Suggestions.

    1. Create a table listing all the products and their corresponding minimum quantity allowed in stock so we could determine when the product must be replenished

    1. Convert it into an Excel table and name it "MinStockTable"
    2. On the data entry sheet. Create/convert your tables into an excel table 

    Column D formula is =B2-C2 and fill it down

    Column E (Status) formula =IF(D2<=VLOOKUP(A2,MinStockTable,2,FALSE),"Replenish","OK") and copy/fill it down.

    1. With the help of slicers you could easily filter your data by the "Replenish" criteria

    Here is the link to the sample file in the pictures   https://we.tl/t-hJ3o1tcuA7

    For more info check the video below

    Do let me know if you need more help.

    Regards

    Jeovany

    https://www.youtube.com/watch?v=gpn9FTitHuE

    4 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-05-12T05:56:24+00:00

    You are very welcome Mike 

    Thanks for the positive feedback 

    Regards 

    Jeovany

    0 comments
  2. Anonymous
    2021-05-12T03:55:46+00:00

    Thank you Jeovany CV.

    Excellent answer. I understand your solution. Very helpful. That will work well.

    Cheers Mike

    0 comments
  3. Anonymous
    2021-05-12T03:53:17+00:00

    Column B is the optimal amount. Column C is in stock, column D is Optimal stock less in stock leaving the amoun that needs to be purchased to return it to optimal stock.

    Cheers

    Mike

    0 comments
  4. Anonymous
    2021-05-12T00:04:45+00:00

    In that work sheet I keep the records of what I have or not

    I am not sure clumn B means(no header).

    why does the out mean stock?

    stock may like below:

    vegetable in out in-stock

    potato 1 0 1=(b2-c2)

    but in your sheet

                      out means in-stock?

                      column b means what ?

    your sum(B1-C1) why not b2-c2 directly?

    sorry I can not get it.

    0 comments