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

Can you post an example?

" wrote:

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

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??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...


" wrote:

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

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


On Aug 17, 7:16*am, firroo wrote:
" wrote:
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...


" wrote:

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

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...


" wrote:

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

Did you like my macro?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...


" wrote:

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

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

"Don Guillett" wrote:


Did you like my macro?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...


" wrote:

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

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

"firroo" wrote:

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

"Don Guillett" wrote:


Did you like my macro?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...


" wrote:

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

people please I need your help regarding this isue

"firroo" wrote:

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

"firroo" wrote:

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

"Don Guillett" wrote:


Did you like my macro?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"firroo" wrote in message
...


" wrote:

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