# Comparing columns and counting matches

#### aberry77

##### New Member
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>

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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>

 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

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.

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

Code:
``````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``````

Replies
5
Views
405
Replies
1
Views
858
Replies
15
Views
590
Replies
1
Views
496
Replies
1
Views
247

1,196,051
Messages
6,013,109
Members
441,748
Latest member
MrBigglesworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back