macro deletion by difference between numbers

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
528
Office Version
  1. 365
Platform
  1. Windows
hello,
i need a macro to delete any row with numbers which doesn't have a difference of 1 AND 2 by an array of predefined numbers
example:
array: 1-2-3-3
check numbers: 6-7-7-8 or 5-7-7-8
explaintion: the difference between highest number in the array is 3 and the lowest in the "check numbers" is 6 (for 6-7-7-8) so lowest difference is 3 so it need to deleted
AND for "check numbers" 5-7-7-8 lowest one is 5 and there's only a 1 difference (of 2) but not 2 differences (1+2) so that also need to deleted
BUT IF the numbers in "check numbers" will be 4-5-6-7 then difference between highest in the array and lowest in "check numbers" will be 1 and 2 (4-3=1 and also 4-2=2 or 5-3=2) so it need to stay
thank you !

here's an example data (with the help of Fluff):
column e is just for showing which lines to delete
any line without the number 3 = in red need to get deleted
(3 indicates there's difference of 1 and 2, 2 indicates of only difference of 2, 1 of only difference of 1, 0 = no difference of either 1 or 2)

example.xlsx
ABCDE
1diff 1+2
211111
311121
411251
512183
612330
767783
845672
912211
1012223
1113173
1213413
1313523
1421111
1521123
1623473
1723483
1823543
1924353
2025513
2125533
2225811
2325823
2426221
2526233
diff_1+2
Cell Formulas
RangeFormula
E2:E25E2=IF(SUMPRODUCT(--(ABS(A2:D2-TRANSPOSE(A3:D3))=1))>0,1,0)+IF(SUMPRODUCT(--(ABS(A2:D2-TRANSPOSE(A3:D3))=2))>0,2,0)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

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