Identifying the last duplicated string

[partho]

Excel Gurus,

I need help, I am trying to find a formula to indicate the last duplicated string in a column by placing a 1 next to it, please see below:




A
B
C
D


Date
Name
Laps
Final Laps Recorded Indicator


5-4
Dan

23









5-6
Billy
21















5-3
Mike
26















5-8
Billy
22
*














5-3
Dan
28
*




















5-10
Mike
26
*




Based on this table above, I want to find the last duplicated name in column B, and put a one by it in column D so it should look like this:
(in my spreadsheet there are empty rows between data because of the way it is imported, so the formula would need to account for that)


A
B
C
D


Date
Name
Laps
Final Laps Recorded Indicator


5-4
Dan

23









5-6
Billy
21









5-3
Mike
26















5-8
Billy
22
1














5-3
Dan
28
1




















5-10
Mike
26
1




I cannot seem to figure it out, any help would be appreciated!

[xladept]

Try this:


Sub Partho(): Dim r As Long, N As String, K
With CreateObject("scripting.Dictionary")
For r = 2 To Range("B" & Rows.count).End(xlUp).Row
If Cells(r, 2) <> "" Then
N = Trim(Cells(r, 2)): .Item(N) = r: End If: Next r
K = .Keys(): For r = LBound(K) To UBound(K)
Cells(.Item(K(r)), 4) = 1: Next r
End With: End Sub

[Rick Rothstein]

If I understand what you want correctly and assuming Row 1 contains the headers and your data starts on Row 2, put this formula in cell D2 and copy it down to the end of your data...

=IF(B2="","",IF(COUNTIF(B2:B$16,B2)=1,1,""))

The red highlighted number needs to be any number equal to or greater than the last row number containing your data (make sure you retain that $ sign that is in front of the number).