excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 510
- Office Version
- 365
- Platform
- Windows
hello,
i'm trying again since last time i didn't got a right solution (and THANKS to the one's who tried )
how can i delete all rows containing any combo of 3 numbers out of 4 predefined numbers ?
range is always a:d for unknown numbers of rows
in here i'm writing two examples,
but the macro should handle only one example=predefined numbers:
example 1:
column e is for predefined numbers 1-2-3-4 and any combo's of 3 unique numbers need to be deleted
like 1-2-3-5 or 4-2-3-8 but not when one of the three is a duplicate one like 1-2-3-3
example 2:
column f is for predefined numbers 5-5-4-3 and any combo's of 3's need to be deleted
but the thing with 5543 is there a duplicate number which is '5', so any combo of 3's in this case will always include a '5'
(554x 5x53 x435 when x could be any number... and so on)
and maybe use an array for me to be able to change when needs to?
y=keep
x=delete
i'm trying again since last time i didn't got a right solution (and THANKS to the one's who tried )
how can i delete all rows containing any combo of 3 numbers out of 4 predefined numbers ?
range is always a:d for unknown numbers of rows
in here i'm writing two examples,
but the macro should handle only one example=predefined numbers:
example 1:
column e is for predefined numbers 1-2-3-4 and any combo's of 3 unique numbers need to be deleted
like 1-2-3-5 or 4-2-3-8 but not when one of the three is a duplicate one like 1-2-3-3
example 2:
column f is for predefined numbers 5-5-4-3 and any combo's of 3's need to be deleted
but the thing with 5543 is there a duplicate number which is '5', so any combo of 3's in this case will always include a '5'
(554x 5x53 x435 when x could be any number... and so on)
and maybe use an array for me to be able to change when needs to?
y=keep
x=delete
| example.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | 1 | 1 | 1 | 1 | y | y | ||
| 2 | 1 | 1 | 1 | 2 | y | y | ||
| 3 | 1 | 1 | 2 | 5 | y | y | ||
| 4 | 1 | 2 | 1 | 8 | y | y | ||
| 5 | 1 | 2 | 2 | 1 | y | y | ||
| 6 | 1 | 2 | 2 | 2 | y | y | ||
| 7 | 1 | 3 | 1 | 7 | y | y | ||
| 8 | 1 | 3 | 4 | 1 | x | y | ||
| 9 | 1 | 3 | 5 | 2 | x | y | ||
| 10 | 2 | 1 | 1 | 1 | y | y | ||
| 11 | 2 | 1 | 1 | 2 | y | y | ||
| 12 | 2 | 3 | 4 | 7 | x | y | ||
| 13 | 2 | 3 | 4 | 8 | x | y | ||
| 14 | 2 | 3 | 5 | 4 | x | x | ||
| 15 | 2 | 4 | 3 | 5 | x | x | ||
| 16 | 2 | 5 | 5 | 1 | y | y | ||
| 17 | 2 | 5 | 5 | 3 | y | x | ||
| 18 | 2 | 5 | 8 | 1 | y | y | ||
| 19 | 2 | 5 | 8 | 2 | y | y | ||
| 20 | 2 | 6 | 2 | 2 | y | y | ||
| 21 | 2 | 6 | 2 | 3 | y | y | ||
| 22 | 2 | 8 | 1 | 2 | y | y | ||
| 23 | 3 | 1 | 3 | 2 | x | y | ||
| 24 | 3 | 2 | 4 | 2 | y | y | ||
| 25 | 3 | 3 | 3 | 5 | y | y | ||
| 26 | 3 | 5 | 3 | 1 | y | y | ||
| 27 | 3 | 5 | 3 | 2 | y | y | ||
| 28 | 4 | 1 | 3 | 4 | x | y | ||
| 29 | 4 | 1 | 5 | 1 | y | y | ||
| 30 | 4 | 1 | 3 | 2 | x | y | ||
| 31 | 4 | 4 | 1 | 5 | y | y | ||
| 32 | 4 | 5 | 1 | 8 | y | y | ||
| 33 | 4 | 5 | 5 | 7 | y | x | ||
| 34 | 5 | 1 | 2 | 1 | y | y | ||
| 35 | 5 | 1 | 2 | 2 | y | y | ||
| 36 | 5 | 3 | 3 | 2 | y | y | ||
| 37 | 5 | 5 | 2 | 7 | y | y | ||
| 38 | 6 | 1 | 3 | 2 | x | y | ||
| 39 | 6 | 1 | 3 | 3 | y | y | ||
| 40 | 6 | 2 | 1 | 6 | y | y | ||
| 41 | 6 | 2 | 1 | 7 | y | y | ||
| 42 | 6 | 4 | 6 | 2 | y | y | ||
| 43 | 6 | 5 | 2 | 6 | y | y | ||
| 44 | 6 | 5 | 2 | 7 | y | y | ||
| 45 | 7 | 1 | 1 | 3 | y | y | ||
| 46 | 7 | 2 | 3 | 4 | x | y | ||
| 47 | 7 | 3 | 3 | 4 | y | y | ||
| 48 | 7 | 4 | 4 | 1 | y | y | ||
| 49 | 7 | 4 | 4 | 2 | y | y | ||
| 50 | 7 | 6 | 5 | 5 | y | y | ||
| 51 | 7 | 6 | 5 | 6 | y | y | ||
| 52 | 8 | 1 | 1 | 1 | y | y | ||
| 53 | 8 | 1 | 2 | 1 | y | y | ||
| 54 | 8 | 1 | 2 | 2 | y | y | ||
| 55 | 8 | 2 | 2 | 2 | y | y | ||
| 56 | 8 | 2 | 2 | 3 | y | y | ||
| 57 | 8 | 3 | 4 | 5 | x | x | ||
| 58 | 8 | 5 | 4 | 3 | y | x | ||
| 59 | 8 | 5 | 5 | 3 | y | x | ||
| 60 | 8 | 5 | 5 | 4 | y | x | ||
| 61 | 8 | 7 | 1 | 1 | y | y | ||
| 62 | 8 | 8 | 3 | 4 | y | y | ||
e-1234+f-5543 | ||||||||
