Formula to Match Debit & credit values

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
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
FGHIJKL
22001200Delete
32001200Delete
47001700Remain
5165PO 23503 Cut key as per example1165Remain
6607.91PO 23506 Supply and fit battery1607.91Delete
7450PO 23505 Assemble lock to suit key1450Remain
84501450Remain
9607.911607.91Delete
104501450Remain
114501450Remain
124501450Remain
Sheet1
 
Last edited:
Give this a try:


Book1
EFGHIJKL
1DateDebitCreditStock NoNarrativePeriodMonthBalanceDelete/Remain
201/10/20152001200Delete
301/10/20152001200Delete
401/10/20157001700Remain
501/10/20151651165Remain
601/10/2015607.911607.91Delete
701/10/20154501450Delete
801/10/20154501450Delete
901/10/2015607.911607.91Delete
1005/10/20151569.1211569.12Remain
1105/10/20151501150Delete
1205/10/20151501150Delete
1305/10/20153601360Remain
1405/10/20151501150Remain
1505/10/20151201120Delete
1605/10/20151201120Delete
1705/10/2015171011710Delete
1805/10/2015171011710Delete
1906/10/20151651165Remain
2006/10/20151719.1211719.12Remain
2107/10/2015170011700Remain
2207/10/20151501150Remain
2308/10/20151001100Remain
2408/10/20151651165Delete
2508/10/2015866.321866.32Remain
2608/10/20151651165Remain
2708/10/2015210.481210.48Delete
2809/10/2015210.481210.48Delete
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Thanks very much for the help. Formula works perfectly
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top