excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 510
- Office Version
- 365
- Platform
- Windows
hi!
i have five numbers, for example:
2-11-4-29-3 each in separate cell,
and i want to check all 120 combinations of them (11-2-4..... 29-2-11... and etc)
against a column of few thousands to see if they match
another problem is i need to check all 120 combinations of each one of the thousands of "original numbers"
can be done?
if too problematic maybe just to check if any combination of said "original number" are a match and count the repeats without actually generate them?
i have five numbers, for example:
2-11-4-29-3 each in separate cell,
and i want to check all 120 combinations of them (11-2-4..... 29-2-11... and etc)
against a column of few thousands to see if they match
another problem is i need to check all 120 combinations of each one of the thousands of "original numbers"
can be done?
if too problematic maybe just to check if any combination of said "original number" are a match and count the repeats without actually generate them?
test.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | |||
1 | 1 | 2 | 3 | 4 | 5 | original combination | all comb's=120 | cheking for repeats | |||
2 | 2 | 11 | 4 | 29 | 3 | 2-11-4-29-3 | 2-11-22-39-29 | 0 | |||
3 | 5 | 5 | 20 | 21 | 25 | 5-5-20-21-25 | 2-11-29-22-39 | 0 | |||
4 | 8 | 8 | 20 | 25 | 30 | 8-8-20-25-30 | 2-22-11-29-39 | 0 | |||
5 | 1 | 4 | 1 | 21 | 2 | 1-4-1-21-2 | and etc | 0 | |||
6 | 5 | 11 | 14 | 22 | 3 | 5-11-14-22-3 | anc etc | 0 | |||
test |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2:I6 | I2 | =C2&"-"&D2&"-"&E2&"-"&F2&"-"&G2 |
K2:K6 | K2 | =IF(COUNTIF(I$1:I2,J2)=0,IF(COUNTIF($I$2:$I$5000,J2)=1,0,COUNTIF($I$2:$I$5000,J2)),"-") |