Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count once, multiple duplicate values? | Excel Worksheet Functions | |||
Count Duplicate Values | Excel Discussion (Misc queries) | |||
How to count duplicate or repeat values | Excel Discussion (Misc queries) | |||
Count on multiple values with duplicate rows | Excel Worksheet Functions | |||
How do I do count calculations ignoring duplicate values | Excel Discussion (Misc queries) |