Formulas for transfer

[firroo]

Hi all,

Can any one invent a formula for this case. I sell the same products in 4
stores, some of the stores are having more quantities of the products
comparing to the quantity needed & the others they are missing products
comparing to the quantity needed. I want a formula which help me to transfer
the products from the stores which they have excess to the stores which they
need those products. People I need your expertise on this as it's so urgent.

Thanks in advanced.

Regards

 

[andrewuk311]

Can you post an example?

 

[firroo]

Can you post an example?

REF store1 store2 store3 store4

ffg 4 -3 6 -1

dsa 1 2 -1 -4

trt -3 -4 6 7

This is an example, is there is a way to attach the file so it will be more
clearer

Thanks

 

[Don Guillett]

If? from your example for ffg store 2 needs 3 you want to take 3 from store
3 leaving 3. Then store 1 can give 1 to store 4???
What would you want done with trt? give store 1 3 from store 4 and give
store 2, four from store 3??

 

[Pete_UK]

I'm not sure exactly what you want to end up with, but with your
example data in A1:E4, you can put these formulae in F2 and G2:

F2: =SUMIF(B2:E2,">0")
G2: =SUMIF(B2:E2,"<0")

and copy these down to row 4. These will show the total of the
positive values and negative values respectively for each product,
i.e.:

sum+ve sum-ve
10 -4
3 -5
13 -7

so you can see at glance that you have more of products ffg and trt
so that you can redistribute them, but you do not have enough of dsa
to satisfy demand.

I'm not sure what you want to do beyond this. In the first case
store1's supply of ffg could be transfered to store2 and store4, but
that would leave store1 with none, so you might prefer to use store3's
stock as that is the largest.

Hope this helps.

Pete

 

[Don Guillett]

Try this. But I don't know what you can do about dsa

Sub findlowandfill()
Range("copynums").Copy Range("a2")
For i = 2 To Cells(rows.Count, "a").End(xlUp).Row
For j = 2 To 5 'columns b:e
If Cells(i, j) < 1 Then
'MsgBox Cells(i, j)
shortage = -Cells(i, j) + 1
'MsgBox shortage
mymax = rows(i).Find(Application.Max(rows(i))).Column
'MsgBox mymax
If Cells(i, mymax) > -Cells(i, j) Then
Cells(i, j) = Cells(i, j) + shortage
Cells(i, mymax) = Cells(i, mymax) - shortage
End If
End If
Next j
Next i
End Sub

 

[firroo]

Dear All,

Thank you so much for trying to help me. To clarify things specially for dsa
of the transfer from the stores were not enough then I will send products
from the warehouse, I include a link below where the file can be downloaded.
I am thanking every one in advanced for his help.

Regards

http://rapidshare.com/files/138017940/To_solve.xls.html

 

[Don Guillett]

Did you like my macro?

 

[firroo]

Actually I am not familiar with macros & I don't know how to use them, sorry
for the negative replay.

 

[firroo]

guys any one can help please. The file is attached. Thanks in advanced.

 

[firroo]

people please I need your help regarding this isue