inventory values to update

[mc136355]

Hi I am trying to update values in an inventory sheet but if value has no change then i cannot create a formula to show the previous value. Any help would be appreciated. Hope this makes sense. thanks MC

cust1 has starting value of 100
cust2 has starting value of 200

Thanks

date	details	type	customer	in	out	cust1	cust2
21/08/19	order	AAA	cust1	500	100	500	200
21/08/19	order	AAA	cust1	100	50	550	200
21/08/19	order	AAA	cust2	50	100	550	150
21/08/19	order	AAA	cust1	100	200	450	150

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	date​	details​	type​	customer​	in​	out​	cust1​	cust2​		Customer​	Starting Value​
2​	21/08/2019​	order​	AAA​	cust1​	500​	100​	500​	200​		cust1​	100​
3​	21/08/2019​	order​	AAA​	cust1​	100​	50​	550​	200​		cust2​	200​
4​	21/08/2019​	order​	AAA​	cust2​	50​	100​	550​	150​
5​	21/08/2019​	order​	AAA​	cust1​	100​	200​	450​	150​
6​

Formula in G2 copied to H2 and down
=VLOOKUP(G$1,$J:$K,2,0)+SUMIF($D$2:$D2,G$1,$E$2:$E2)-SUMIF($D$2:$D2,G$1,$F$2:$F2)

M.

 

[mc136355]

Hi MB thanks for the quick reply. Looks like this has done the trick and is working on both columns. Thanks for your time. MC

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.