Count duplicate values in worksheet

[Guest]

Hi all,

does anyone knows how to write a formula to count the no. of duplicate vales
in a column?

For example:

Yes
No
Yes
No
Yes

When we apply the formula to a cell based on the value "Yes", the cell will
show 3.
When we apply the same formula in another cell, the cell value will show 2.

The Count formula in Excel counts only numeric values.

Any help is appreciated.

Thanks in advance.

Cheers,
HS

 

[Ron Rosenfeld]

Hi all,

does anyone knows how to write a formula to count the no. of duplicate vales
in a column?

For example:

Yes
No
Yes
No
Yes

When we apply the formula to a cell based on the value "Yes", the cell will
show 3.
When we apply the same formula in another cell, the cell value will show 2.

The Count formula in Excel counts only numeric values.

Any help is appreciated.

Thanks in advance.

Cheers,
HS

=COUNTIF(colA,"Yes")


--ron

 

[Mike Fogleman]

The COUNTA function will count text. I assume you want a macro to do this,
so try this:

Sub Count_Duplicates()
Dim iCt As Long
Dim iRow As Long
Dim c As Range
Dim c2 As Range

iRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
For Each c In Sheets("Sheet1").Range("A1:A" & iRow)
If c <> "" And c.Offset(0, 3) <> "x" Then
iCt = 1
For Each c2 In Sheets("Sheet1").Range("A1:A" & iRow)
If c.Row <> c2.Row And c2.Offset(0, 3) <> "x" Then
If c = c2 And c.Offset(0, 1) = c2.Offset(0, 1) _
And c.Offset(0, 2) = c2.Offset(0, 2) Then
iCt = iCt + 1
c2.Offset(0, 3) = "x"
End If
End If
Next c2
c.Offset(0, 3) = iCt 'Add -1 to not count the first instance
End If
Next c
For iCt = iRow To 2 Step -1
If Sheets("Sheet1").Cells(iCt, 4) = "x" _
Then Sheets("Sheet1").Rows(iCt).Delete
Next iCt

Columns("A").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub

With your list in column A, it will delete all duplicates, leaving 1 each
unique item with the count of how many there were in column D, then sort
them according to the count in descending order.

Mike F