counting wrong?

[excelNewbie22]

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

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)),"-")

 

[etaf]

=countif($A$2:$A$10,B2)
and copy down

Book1
	A	B	C	D
1	list a	list b	repeats
2	11-3-4-8-4	11-3-4-8-4	0	1
3	14-1-15-1-1	11-3-4-4-8	0	0
4	2-8-2-12-11	11-3-8-4-4	0	0
5	3-3-14-1-14	11-4-3-8-4	0	0
6	5-9-2-4-3	11-4-3-4-8	0	1
7	2-1-5-5-10	11-4-8-3-4	0	0
8	11-4-3-4-8	11-4-8-4-3	0	0
9	3-4-8-4-11	11-4-4-3-8	0	0
10	2-1-4-5-21	11-4-4-8-3	0	0
11		11-8-3-4-4	0	0
12		11-8-4-3-4	0	0
13		11-8-4-4-3	0	0
14		3-11-4-8-4	0	0
15		3-11-4-4-8	0	0
16		3-11-8-4-4	0	0
17		3-4-11-8-4	0	0
18		3-4-11-4-8	0	0
19		3-4-8-11-4	0	0
20		3-4-8-4-11	-	1
21		3-4-4-11-8	0	0
Sheet1

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)),"-")
D2:D21	D2	=COUNTIF($A$2:$A$10,B2)

 

[excelNewbie22]

thank you
but what if there will be duplicates in list b?
won't it count it twice?

edit:
also, i need to know mostly if and how many it repeats more then once
sorry not to mention it sonner

 

[etaf]

can you show some examples of what you mean please,

with examples of expected results

 

[excelNewbie22]

i think the problem is when i have two identicals in list b
see d10 vs e10

testtttttt.xlsx
	A	B	C	D	E
1	list a	list b	repeats	expected results	COUNTIF($A$2:$A$10,B2)
2	11-3-4-8-4	11-3-4-8-4	0	1	1
3	3-4-8-4-11	11-3-4-4-8	0	0	0
4	2-8-2-12-11	11-3-8-4-4	0	0	0
5	11-4-8-4-3	11-4-3-8-4	0	0	0
6	5-9-2-4-3	11-4-3-4-8	0	1	1
7	2-1-5-5-10	11-4-8-3-4	0	0	0
8	11-4-3-4-8	11-4-8-4-3	-	1	1
9	3-4-8-4-11	11-4-4-3-8	0	0	0
10	2-1-4-5-21	11-4-8-4-3	-	-	1
11		11-8-3-4-4	0	0	0
12		11-8-4-3-4	0	0	0
13		11-8-4-4-3	0	0	0
14		3-11-4-8-4	0	0	0
15		3-11-4-4-8	0	0	0
16		3-11-8-4-4	0	0	0
17		3-4-11-8-4	0	0	0
18		3-4-11-4-8	0	0	0
19		3-4-8-11-4	0	0	0
20		3-4-8-4-11	-	2	2
21		3-4-4-11-8	0	0	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)),"-")
E2:E21	E2	=COUNTIF($A$2:$A$10,B2)

 

[etaf]

how about
=IF(COUNTIF($B$2:B2,B2)>1,"-",COUNTIF($A$2:$A$10,B2))

Book3
	A	B	C	D	E	F
1	list a	list b	repeats	expected results	COUNTIF($A$2:$A$10,B2)
2	11-3-4-8-4	11-3-4-8-4	0	1	1	1
3	3-4-8-4-11	11-3-4-4-8	0	0	0	0
4	2-8-2-12-11	11-3-8-4-4	0	0	0	0
5	11-4-8-4-3	11-4-3-8-4	0	0	0	0
6	5-9-2-4-3	11-4-3-4-8	0	1	1	1
7	2-1-5-5-10	11-4-8-3-4	0	0	0	0
8	11-4-3-4-8	11-4-8-4-3	-	1	1	1
9	3-4-8-4-11	11-4-4-3-8	0	0	0	0
10	2-1-4-5-21	11-4-8-4-3	-	-	1	-
11		11-8-3-4-4	0	0	0	0
12		11-8-4-3-4	0	0	0	0
13		11-8-4-4-3	0	0	0	0
14		3-11-4-8-4	0	0	0	0
15		3-11-4-4-8	0	0	0	0
16		3-11-8-4-4	0	0	0	0
17		3-4-11-8-4	0	0	0	0
18		3-4-11-4-8	0	0	0	0
19		3-4-8-11-4	0	0	0	0
20		3-4-8-4-11	-	2	2	2
21		3-4-4-11-8	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
E2:E21	E2	=COUNTIF($A$2:$A$10,B2)
F2:F21	F2	=IF(COUNTIF($B$2:B2,B2)>1,"-",COUNTIF($A$2:$A$10,B2))
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)),"-")

 

[excelNewbie22]

thank you etaf !

 

[etaf]

you are welcome