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).