Count duplicate values in worksheet
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 |
Count duplicate values in worksheet
On Tue, 17 May 2005 19:35:11 -0700, "Quek HS"
wrote: 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 |
Count duplicate values in worksheet
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:D").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 "Quek HS" wrote in message ... 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 |
All times are GMT +1. The time now is 12:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com