Formula to create store by store inventory transfers

Hi Everyone,

This is the first time I have ever posted to an online forum after having been a long time reader! I am hoping someone is able to assist with a rather complex request. I have included the data in the attached spreadsheet as well as the output we are seeking to get. In short we need help with a formula to prepare a list of items that Shop 1 needs to transfer to Shop 2, Shop 3, Shop 4, Shop 5 etc and then a list of items Shop 2 needs to transfer to Shop1, Shop 3, Shop 4, etc.

Kind regards,

Damian

brilliant question yes it is possible for a store to receive 2.kind regards Damian

phew.
Not so easy as I thought in a first place.

I've create output in SECOND sheet so you can add/remove more store or products.
You a flexible with it as well as names of such stores/products.

Here, try this. Press button next to the table and find result in Output sheet.

Please Login or Register  to view this content.

In main sheet you can write anything as long as data AROUND table is empty.
Don't write in output sheet as it's deleted each time you run macro.

Or copy paste your data to new sheet or main sheet in case you want to preserve some comments.

Originally Posted by sourabhg98

@zbor
As specified by Damian there is chance of having more than 1 for expected delivery.
That code doesn't seem to work if we have say -1,-1,-2,-1,3,0,1,0,1 respectively for the 9 shops.

Thank you for that.
I've worked only with -1 values.

Fixed now.

It's not easy to follow it because I don't know how to explain it shortly

I would just suggest for your code to declare sheet names at the top since in that case, if you change sheet name you need to do that only at one place.

I'll try to explain:

First delete old sheet and isert new one:

Please Login or Register  to view this content.

Then create output data:

Please Login or Register  to view this content.

That will result with shoprs, products and items

Please Login or Register  to view this content.

Then insert "Expect a delivery of this item" whenever is negative (we can put, as you did, also number of negative items).
This part is easy so far.

Please Login or Register  to view this content.

Result

Please Login or Register  to view this content.

Now go for each item

Please Login or Register  to view this content.

check is it > 0 so it can give item to other stores

Please Login or Register  to view this content.

Then start loop from the start to check wich stores need item

Please Login or Register  to view this content.

and check again is some of item remain from previous adding

Please Login or Register  to view this content.

if current cell is still negative

Please Login or Register  to view this content.

Add items to the negative store
Remove items from positive store
and transfer rest for other stores (if has any)

Please Login or Register  to view this content.

Add that store to transfering stores (i.e. Transfer this item to: Shop 1, Shop 2)

Please Login or Register  to view this content.

And now delete that third column with values.

I thought to do that first.
(If you look at mine code you'll see I'm usign ws1 and ws3. ws2 was for that table )

But at the end I figure out I can go without it.

There is also one thing to consider that:

Transfer this item to: Shop 1, Shop 2

Doesn't say how much items going where.

I've fix it into:

Transfer item(s): 3 to Shop 1, 1 to Shop 2, 1 to Shop 3

This is output result:

Please Login or Register  to view this content.

Wow....I am totally blown away by your brilliance. This was a perfect solution - I am so grateful to you both for taking the time and showing such professionalism in helping me resolve this issue.

Wishing you both well.

Warm regards,

Damian

Hope to see you again

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.