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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
*
*
*	*
*	*
*	*
*	*
*	*

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #ffff00, align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #ffff00, align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #ffff00, align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

</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
*
*
*
*	*
*	*
*	*
*	*
*	*

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ffff00, align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ffff00, align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #ffff00, align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

</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