Comparing columns and counting matches

[aberry77]

I've tried using various count formulas but have been unable to get this to work the way I would like. I'm running Excel 2007. If you need more specifics let me know. I appreciate any tips or advise on how to get this to work.

I need 4 things...
1. Compare Col A & Col B and count number of matches
2. Count number of duplicate records in Col A
3. Count number of duplicate records in Col B
4. Count number of records that have no match in Col A & Col B

Example: Col C would hold the results

C1 = Matches in Col A & B
C2 = Dups in Col A
C3 = Dups in Col B
C4 = No matches Col A
C5 = No matches Col B

1	1	7
2	2	1
3	3	2
3	4	2
4	4	1
5	6
7	7
8	8
9	10
10	10

<tbody>
</tbody>

 

[venkat1926]

see the formula in col D. colc C is your results kept for comparison

the other two queries are not clear
Sheet1

*	A	B	C	D
1	1	1	7	7
2	2	2	1	1
3	3	3	2	2
4	3	4	2	*
5	4	4	1	*
6	5	6	*	*
7	7	7	*	*
8	8	8	*	*
9	9	10	*	*
10	10	10	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D1 =SUMPRODUCT((A1:A10=B1:B10)*1) D2 =SUM(IF(FREQUENCY(A1:A10,A1:A10)>1,1)) D3 =SUM(IF(FREQUENCY(B1:B10,B1:B10)>1,1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[aberry77]

the other two queries are not clear

Thank you for your reply. The formulas you gave work perfect! As for the unclear queries I would like to be able to count the number of items in Col A that do not have a match in Col B. Likewise for any items in Col B that do not have a match in Col A. I hope that makes more sense. Thanks for taking the time. I appreciate it.

 

[venkat1926]

there are 3 not 2 see the sheet see D4 and D5

Sheet1

*	A	B	C	D
1	1	1	7	*
2	2	2	1	*
3	3	3	2	*
4	3	4	2	3
5	4	4	1	3
6	5	6	*	*
7	7	7	*	*
8	8	8	*	*
9	9	10	*	*
10	10	10	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


will not be the other query reply be the same. if not explain

Immediately I am not able to think of a formula but a macro given below

Sub A_not_B()
Dim r As Range, c As Range, j As Integer, k As Integer
k = 0
j = 0
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In r
If c <> c.Offset(0, 1) Then
c.Interior.ColorIndex = 6
j = j + 1
End If
Next c
Range("D4") = j
End Sub