Redistribute Inventory to Locations Where There is Need

[krystal007]

Hello.

Sample Data is attached.

I have a fun problem to solve regarding inventory at specific warehouses. Each warehouse should have x inventory referred to as "Inventory Needed". Some warehouses have inventory above this level ("Excess") while other warehouses have inventory less than what is needed ("Need"). The inventory of each "Product" on hand at each warehouse is referred to as "Total Inventory Available".

Warehouse Name	Product	Inventory Needed	Total Inventory Available	Excess	Need
Orange Drive	406200	3	0	0	3
Purple Blvd.	406200	6	7	1	0
Green Street	407969	4	5	1	0
Yellow Blvd.	407969	9	12	3	0
Blue Street	414017	3	0	0	3
Green Street	414017	5	3	0	2
Orange Drive	414017	5	8	3	0
Orange Drive	443029	7	8	1	0
Red Street	443029	4	2	0	2
Blue Street	454392	6	9	3	0
Orange Drive	454392	5	5	0	0
Red Street	476585	10	10	0	0
Blue Street	478624	4	3	0	1
Red Street	478624	7	8	1	0

I would like to begin writing some code to that would loop through each warehouse and identify if there is any excess units of that product at a different warehouse that can be moved to fill any inventory needs. The end result would be a print out of each inventory transfer that needs to occur in order to redistribute including the name of the sending warehouse, the product, quantity being transferred, and the receiving warehouse.

Sending Warehouse	Product	Quantity	Receiving Warehouse
Purple Blvd.	406200	1	Orange Drive
Orange Drive	414017	3	Blue Street
Orange Drive	443029	1	Red Street
Red Street	478624	1	Blue Street

I have attempted to achieve this result using some fun and fancy rolling formulas involving waaaay too many columns. It worked-ish with the biggest issue being my output including the redistribution of the same inventory to multiple different warehouses.

I did my best to search for a thread that covered my request, but could not find anything. Does anyone have any code that I can explore? Or a link to a similar thread or topic you find online? I'm certain I'm not finding any results because of poor wording on my part.

Thank you in advance!

[dangelor]

Possibly...

Please Login or Register  to view this content.

[Kaper]

If i can add my 5 cents …

Please Login or Register  to view this content.

Blue parts will help to handle such situation as:

Blue Street 414017 3 0 0 3
Green Street 414017 5 3 0 3
Orange Drive 414017 5 8 5 0
Red Street 414017 5 8 1 0

--->

Orange Drive 414017 3 Blue Street
Orange Drive 414017 2 Green Street
Red Street 414017 1 Green Street

Green will write ammended array back to worksheet (I expect in real life the same as it was original - but of course it is always a good idea to show both "before" and "after" situations.

[krystal007]

Thank you both so very much. This is exactly what I needed.

I am doing my best to now actually learn VBA rather than just copy, pasting, and creating little spreadsheet abominations.

Would you mind explaining the logic behind this code? What is it doing?

Or alternatively, if you have any websites or other threads you can recommend I read up on to help, I would gladly like to do that.

Either way, thank you both again. I'm going to do my best to comment the code myself to try to gain some understanding of what I think is going on.

[dangelor]

With comments...

Please Login or Register  to view this content.

I started just like this - asking questions on forums, then attempting to answer some as my knowledge grew. Helping others helps me stretch my knowledge and I learn as I help. For a site to learn good vba basics, I would suggest Excel Macro Mastery, and for a book on all of excel, J. Walkenbach's Spreadsheet Bible, although I don't think it's in print now. Good luck!

[Kaper]

Also comments to my propositions:

Please Login or Register  to view this content.