excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 510
- Office Version
- 365
- Platform
- Windows
hi,
i have two lists, list a and list b
i want to check each cell in list b against all list a to check if there's repeats
but for some reason it doesn't work
why is that?
also, is it a problem list a shorter then list b for this formula? cause when the formula get to c12 it checks against empty cells
i have two lists, list a and list b
i want to check each cell in list b against all list a to check if there's repeats
but for some reason it doesn't work
why is that?
also, is it a problem list a shorter then list b for this formula? cause when the formula get to c12 it checks against empty cells
testtttttt.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | list a | list b | repeats | ||
2 | 11-3-4-8-4 | 11-3-4-8-4 | 0 | ||
3 | 14-1-15-1-1 | 11-3-4-4-8 | 0 | ||
4 | 2-8-2-12-11 | 11-3-8-4-4 | 0 | ||
5 | 3-3-14-1-14 | 11-4-3-8-4 | 0 | ||
6 | 5-9-2-4-3 | 11-4-3-4-8 | 0 | ||
7 | 2-1-5-5-10 | 11-4-8-3-4 | 0 | ||
8 | 11-4-3-4-8 | 11-4-8-4-3 | 0 | ||
9 | 3-4-8-4-11 | 11-4-4-3-8 | 0 | ||
10 | 2-1-4-5-21 | 11-4-4-8-3 | 0 | ||
11 | 11-8-3-4-4 | 0 | |||
12 | 11-8-4-3-4 | 0 | |||
13 | 11-8-4-4-3 | 0 | |||
14 | 3-11-4-8-4 | 0 | |||
15 | 3-11-4-4-8 | 0 | |||
16 | 3-11-8-4-4 | 0 | |||
17 | 3-4-11-8-4 | 0 | |||
18 | 3-4-11-4-8 | 0 | |||
19 | 3-4-8-11-4 | 0 | |||
20 | 3-4-8-4-11 | - | |||
21 | 3-4-4-11-8 | 0 | |||
test |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C21 | C2 | =IF(COUNTIF($A$1:A1,B2)=0,IF(COUNTIF($A$2:$A$10,B2)=1,0,COUNTIF($A$2:$A$10,B2)),"-") |