Well, I got this:
Col D
9
1
0

I think it should be G instead of H,
=VLOOKUP(A2;$A$2:$C$4;3;0)-IF(A2=$E$2;$G$2;0)-IF($E$1=A2;$G$1;0)+IF(A2=$F$1;$G$1;0)+IF(A2=$F$2;$ G$2;0)

and I got this:
Col D
6
4
0

But how to know the source & destination shop?

--
Whatever you love doing. keep doing it.


"muddan madhu" wrote:

try this

assumed you have details like this
Col A Col B Col C
Shop Sale Inventory
1 2 9
2 10 1
3 4 0

Transfer details
Col E Col F Col G
shop shop no. of pcs
1 2 3
1 2 3

Col D
put this formula and drag it down
=VLOOKUP(A2,$A$2:$C$4,3,0)-IF(A2=$F$2,$H$2,0)-IF($F$1=A2,$H$1,0)+IF(A2=
$G$1,$H$1,0)+IF(A2=$G$2,$H$2,0)


On Jan 10, 5:45 pm, bestman21
wrote:
Any suggestions to did this?

Example 1:

shop1,sale = 2 inventory = 9
shop2,sale =10 inventory = 1
shop3,sale = 4 inventory = 0
Total QTY,sale = 16 inventory = 10

Transfer:
shop1 to shop2 = 3 pcs
shop1 to shop3 = 3 pcs

Desired:

shop1,sale = 2 inventory = 3
shop2,sale =10 inventory = 4
shop3,sale = 4 inventory = 3
Total QTY,sale = 16 inventory = 10

===================================

Example 2:

shop1,sale = 10 inventory = 5
shop2,sale = 9 inventory = 1
shop3,sale = 4 inventory = 11
Total QTY,sale = 23 inventory = 17

Transfer:
shop3 to shop2 = 5 pcs

Desired:

shop1,sale = 10 inventory = 5
shop2,sale = 9 inventory = 6
shop3,sale = 4 inventory = 6
Total QTY,sale = 23 inventory = 17
--
Whatever you love doing. keep doing it.