excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 534
- Office Version
- 365
- Platform
- 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)
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 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | diff 1+2 | ||||||
2 | 1 | 1 | 1 | 1 | 1 | ||
3 | 1 | 1 | 1 | 2 | 1 | ||
4 | 1 | 1 | 2 | 5 | 1 | ||
5 | 1 | 2 | 1 | 8 | 3 | ||
6 | 1 | 2 | 3 | 3 | 0 | ||
7 | 6 | 7 | 7 | 8 | 3 | ||
8 | 4 | 5 | 6 | 7 | 2 | ||
9 | 1 | 2 | 2 | 1 | 1 | ||
10 | 1 | 2 | 2 | 2 | 3 | ||
11 | 1 | 3 | 1 | 7 | 3 | ||
12 | 1 | 3 | 4 | 1 | 3 | ||
13 | 1 | 3 | 5 | 2 | 3 | ||
14 | 2 | 1 | 1 | 1 | 1 | ||
15 | 2 | 1 | 1 | 2 | 3 | ||
16 | 2 | 3 | 4 | 7 | 3 | ||
17 | 2 | 3 | 4 | 8 | 3 | ||
18 | 2 | 3 | 5 | 4 | 3 | ||
19 | 2 | 4 | 3 | 5 | 3 | ||
20 | 2 | 5 | 5 | 1 | 3 | ||
21 | 2 | 5 | 5 | 3 | 3 | ||
22 | 2 | 5 | 8 | 1 | 1 | ||
23 | 2 | 5 | 8 | 2 | 3 | ||
24 | 2 | 6 | 2 | 2 | 1 | ||
25 | 2 | 6 | 2 | 3 | 3 | ||
diff_1+2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E25 | E2 | =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. |