Find nth duplicate in a range
This tutorial shows how to find the nth duplicate value in a range using an Excel formula or VBA
Example: Find nth duplicate in a range
|
This formula uses the Excel IF and COUNTIF functions to find the nth duplicate value in a selected range and return a specific value. In this example when the formula finds the second duplicate value in the selected range (B5:B10) it will return the words 'Second Duplicate' in the cell next to the second duplicate value that it found. It will return a blank cell next to all other occurrences of this value from the selected range.
|
Sub Find_nth_duplicate_in_range()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'find second duplicate value and return a specific value in the cell to the right
For x = 5 To 10
For x = 5 To 10
If WorksheetFunction.CountIf(ws.Range("$B$5:$B$10"), ws.Range("B" & x)) > 1 Then
If WorksheetFunction.CountIf(ws.Range("$B$5:B" & x), ws.Range("B" & x)) = 2 Then
ws.Range("C" & x) = "Second Duplicate"
Else
ws.Range("C" & x) = ""
End If
ws.Range("C" & x) = "Second Duplicate"
Else
ws.Range("C" & x) = ""
End If
End If
Next x
End Sub
EXPLANATION
This tutorial shows how to find the nth duplicate value from a selected range through the use of an Excel formula or VBA.
The Excel method uses the IF and COUNTIF functions to find the nth duplicate value in a specified range and return a specific value.
The VBA method adopts a very similar approach by also using the If and Countif functions but it also uses a For function to loop through each of the cells in the specific range to test for the nth duplicate value and return a specific value in the nominated cell.
FORMULA
=IF(COUNTIF(range,value)>1,IF(COUNTIF(limit_range,value)=nth_duplicate,"return_value",""),"")
=IF(COUNTIF(range,value)>1,IF(COUNTIF(limit_range,value)=nth_duplicate,"return_value",""),"")
ARGUMENTS
range: A range of cells in which you want to find the nth duplicate value.
value: The value that you are testing if it's the nth duplicate value.
limit_range: A range up to and including the cell that you are testing if it's the nth duplicate value in the range.
result_value: A value to return if it's the nth duplicate value.
nth_duplicate: A number that represents the nth duplicate value that you want to find in the range.
range: A range of cells in which you want to find the nth duplicate value.
value: The value that you are testing if it's the nth duplicate value.
limit_range: A range up to and including the cell that you are testing if it's the nth duplicate value in the range.
result_value: A value to return if it's the nth duplicate value.
nth_duplicate: A number that represents the nth duplicate value that you want to find in the range.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Find first duplicate in a range | How to find the first duplicate value in a range using Excel and VBA | |
| Find duplicate values in a range | How to find duplicate values in a range using Excel and VBA | |
| Count duplicate values | How to count duplicate values in a range using Excel and VBA | |
| Count duplicate values in order | How to count duplicate values in order using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| COUNTIF Function | The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria |