Formula to Match Debit & credit values

[howard]

I have debit values in Col F & credit values in Col G the balance in Col K


I have tried to set up a formula so that where the debit and credit balances are the same, then "delete" to appear in Col L otherwise "remain" to appear


The formula works for some of the items. The items in Yellow should appear as delete.


It would be appreciated if someone could kindly amend my formula to accommodate my requirements

Excel 2012
	F	G	H	I	J	K	L
2	200				1	200	Delete
3		200			1	200	Delete
4		700			1	700	Remain
5	165			PO 23503 Cut key as per example	1	165	Remain
6	607.91			PO 23506 Supply and fit battery	1	607.91	Delete
7	450			PO 23505 Assemble lock to suit key	1	450	Remain
8		450			1	450	Remain
9		607.91			1	607.91	Delete
10		450			1	450	Remain
11	450				1	450	Remain
12		450			1	450	Remain
Sheet1

 

[wideboydixon]

Give this a try:

Book1
	E	F	G	H	I	J	K	L
1	Date	Debit	Credit	Stock No	Narrative	PeriodMonth	Balance	Delete/Remain
2	01/10/2015	200				1	200	Delete
3	01/10/2015		200			1	200	Delete
4	01/10/2015		700			1	700	Remain
5	01/10/2015	165				1	165	Remain
6	01/10/2015	607.91				1	607.91	Delete
7	01/10/2015	450				1	450	Delete
8	01/10/2015		450			1	450	Delete
9	01/10/2015		607.91			1	607.91	Delete
10	05/10/2015	1569.12				1	1569.12	Remain
11	05/10/2015	150				1	150	Delete
12	05/10/2015		150			1	150	Delete
13	05/10/2015	360				1	360	Remain
14	05/10/2015	150				1	150	Remain
15	05/10/2015	120				1	120	Delete
16	05/10/2015		120			1	120	Delete
17	05/10/2015	1710				1	1710	Delete
18	05/10/2015		1710			1	1710	Delete
19	06/10/2015		165			1	165	Remain
20	06/10/2015		1719.12			1	1719.12	Remain
21	07/10/2015	1700				1	1700	Remain
22	07/10/2015	150				1	150	Remain
23	08/10/2015	100				1	100	Remain
24	08/10/2015	165				1	165	Delete
25	08/10/2015	866.32				1	866.32	Remain
26	08/10/2015		165			1	165	Remain
27	08/10/2015	210.48				1	210.48	Delete
28	09/10/2015		210.48			1	210.48	Delete
Sheet1

Cell Formulas
Range	Formula
K2	=+F2+G2
L2	=IF($F2>0,IFERROR(IF(INDEX($E3:$E1000,MATCH($F2,$G3:$G1000,0))<=$E2+2,"Delete","Remain"),"Remain"),IFERROR(IF(INDEX($E$1:$E1,MATCH($G2,$F$1:$F1,0))>=$E2-2,"Delete","Remain"),"Remain"))

WBD

 

[howard]

Thanks very much for the help. Formula works perfectly