Forum Discussion
Danger_SF
Nov 22, 2021Brass Contributor
Count of duplicates in a column
Good morning!
Is there a formula that can be used to give me a count of duplicate values in a column?
To be clear, I'm not trying to count known duplicate values that I can include in a COUNTIF formula, such as =COUNTIF(A:A),"Specific Text". I'm looking for Excel to simply count how many times it sees a duplicate of any kind. See attached.
leisaellemor To automate this with VBA, open the Visual Basic editor (Alt+F11). In the left-hand pane under VBAProject > Microsoft Excel Objects, double-click the sheet name that contains your data to open the worksheet module. The code will vary slightly, depending on if your data has been formatted as an Excel Table, or if it's just a regular worksheet range.
Version 1: With a Structured Excel Table
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'Stop macro if more than once cell was changed If Target.Cells.Count > 1 Then Exit Sub 'Apply filter if the criteria cell was changed Dim rg As Range Set rg = Me.Range("C1") If Not Intersect(Target, rg) Is Nothing Then Me.ListObjects(1).Range.AutoFilter Field:=3, criteria1:="<=" & rg.Value End If End Sub
Version 2: With a Worksheet Range
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'Stop macro if more than once cell was changed If Target.Cells.Count > 1 Then Exit Sub 'Apply filter if the criteria cell was changed Dim rg As Range Set rg = Me.Range("C1") If Not Intersect(Target, rg) Is Nothing Then Dim rgData As Range Set rgData = Me.Range("A2").CurrentRegion Set rgData = rgData.Offset(1).Resize(rgData.Rows.Count - 1) rgData.AutoFilter Field:=3, Criteria1:="<=" & rg.Value End If End Sub
Note: since the screenshot of your data does not reveal the worksheet column letters or rows numbers, I made the assumption that the table begins in cell A2, the column to filter is Field:=3 and the criteria to filter by is in cell C1. Please adjust the range references and field number, if necessary.
Also, Range("A2").CurrentRegion was used here to identify the entire data range. This method will only work properly if your data range does not contain any completely blank rows or columns... the CurrentRegion method identifies all adjacent data in the same manner that pressing Ctrl+Shift+8 (*) will highlight all adjacent data to the active cell. If your data contains additional adjacent cells above the header row, you may need to adjust the Offset and Resize numbers accordingly.
Then, just input a new value in the criteria cell to refresh the filter.
5 Replies
Sort By
- articulateCopper Contributor
Can anyone help with this issue I'm having please?
When I try to count the unique values in the array (A1:Q27) in the sheet, it's returning 405 (cell A29) and I've no idea why? There are only 105 non-blank values in the array, and 459 possible cells, so I don't know where 405 is coming from?!
This is because it is a two-dimensional range. Convert it to a single column first:
=COUNTA(UNIQUE(TOCOL(A1:Q27,1)))
The second argument 1 in TOCOL makes Excel ignore blank cells.
- articulateCopper Contributor
Thank you so much, I didn't realise unique would only run on a single column/row.
If you want the number of unique entries:
=COUNTA(UNIQUE(D3:D12))
If you want the number of entries that occur more than once:
=SUM(--(COUNTIF(D3:D12,UNIQUE(D3:D12))>1))
- Danger_SFBrass ContributorThank you for your help.