Product movement from one stock to another

[#1]

Hi guys!

I have very important issue, please give an advice how to solve.

For example, I have 5 stocks. And some products in it. This is my table:

	Product	Shop1	Shop2	Shop3	Shop4	Shop5	Stock1
Sales	Item1	5	4	3	2	0	1
Remainder		0	3	0	1	1	1

Now I see that Shop1 has 0 of product Item1 but the shop made best sales.

So, I have to take product from Stock1 (first of all - if there exist products) and put it to Shop1.

Then I see that Shop2 has >0 remained products, so I skip it.

Then I see Shop3 has 0 of product. Now I will take it from Shop5, because it had 0 sales.

Finally I must have this table:

	Product	Shop1	Shop2	Shop3	Shop4	Shop5	Stock1
Sales	Item1	5	4	3	2	0	1
Remainder		0	3	0	1	1	1
Distributed		1	3	1	1	0	0

How to make it?

Please help

[Anonymous]

Hi

I tried with PIVOT table .

[#3]

Thank you Allu!

Good example. But actually we have such steps:

1) Sort by sales.

2) Search for 0 values in shops

3) If some shop has 0 remained items and sales >0, then take firstly from stock (if there exist anything), if there is nothing take from such shop that has >1 items remained

4) If there are very few items remained, so we could not fulfill every shop, then fulfill the best saled shops

[#4]

May be somebody could give some hint how to make this steps?

1) Sort by sales.

2) Search for 0 values remained in shops

3) If some shop has 0 remained items and sales >0, then take firstly from stock (if there exist anything), if there is nothing, take from such shop that has >1 items remained

4) If there are very few items remained, so we could not fulfill every shop, then fulfill the best selling shops