index or match or??

[bestman21]

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

 

[muddan madhu]

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)

 

[bestman21]

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?