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]

Not sure I understand. Copying the formula down from L2 shows "Delete" for those cells. What's the problem?

WBD

 

[AkaTrouble]

Not sure I understand. Copying the formula down from L2 shows "Delete" for those cells. What's the problem?

WBD

may be a silly question

how are you viewing the formula ???

i copied the table into excel and only get values

regards

 

[wideboydixon]

may be a silly question

how are you viewing the formula ???

i copied the table into excel and only get values

regards

Haha!! The OP removed the formulas when the post was edited! It was originally this:

Book1
	F	G	H	I	J	K	L
1	Debit	Credit	Stock No	Narrative	PeriodMonth	Balance	Delete Row
2	200				1	200	Delete
3		200			1	200	Delete
4		700			1	700	Remain
5	165				1	165	Remain
6	607.91				1	607.91	Delete
7	450				1	450	Delete
8		450			1	450	Delete
9		607.91			1	607.91	Delete
10	1569.12				1	1569.12	Remain
11	150				1	150	Delete
12		150			1	150	Delete
Sheet1

Cell Formulas
Range	Formula
K2	=+F2+G2
L2	=IF(COUNTIF(F:F,K2)=COUNTIF(G:G,K2),"Delete","Remain")

WBD

 

[AkaTrouble]

Haha!! The OP removed the formulas when the post was edited! WBD

Thanks. Am glad it not some secret illuminate club that i missed joining lol

 

[howard]

See sample data with formulas

Excel 2012
	F	G	H	I	J	K	L
1	Debit	Credit	Stock No	Narrative	PeriodMonth	Balance	Delete Row
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
13
14
15
16
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
K2	=+F2+G2
L2	=IF(COUNTIF(F:F,K2)=COUNTIF(G:G,K2),"Delete","Remain")

 

[TonyUK72]

Your formula & spreadsheet have no way of link each debit with each credit. What happens if two different purchasers spend the same amount, one could pay and both could be marked as paid?

 

[howard]

Thanks Tony. Fully understand the situation. one would need a common account or reference to match these off

 

[howard]

The way to match the items is the if the amount in Col F = Col G and the date for the debit amount is either the same as the amount of the credit amount or max two days after then "delete" to appear in Col L or Remain


See sample data below

It would be appreciated if someone could amend my formula

Excel 2012
	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	Delete
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	Remain
12	05/10/2015		150			1	150	Remain
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	Delete
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	Delete
27	08/10/2015	210.48				1	210.48	Delete
28	09/10/2015		210.48			1	210.48	Delete
29
Sheet1

Cell Formulas
Range	Formula
K2	=+F2+G2
L2	=IF(COUNTIF(F:F,K2)=COUNTIF(G:G,K2),"Delete","Remain")

 

[howard]

I have come up with a solution of the dates match, but need it amended to add max two days

=IF(AND(E2=E3),IF(COUNTIF(F:F,K2)=COUNTIF(G:G,K2),"Delete","Remain"),"remain")