如何在同一列中找到每个重复项的最后一行索引？

Question

我正在尝试创建一个程序，以便它能够找到位于同一列中的每个重复项的最后一行索引，并存储它们的值。例如，在图片中，约翰、王牌、爱丽丝和莎拉的最后一行名字索引应该分别给我13,17,23,26。目前，我的代码只能识别副本，所以我可以做什么来找到每个重复的最后一行索引，不仅是我显示的图片，而且所有的情况都是如此？

​

​

Sub Testing()

    Dim mycell As Range, RANG As Range

    With Sheets(1)

        ' Build a range (RANG) between cell F2 and the last cell in column F
        Set RANG = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))

    End With

    ' For each cell (mycell) in this range (RANG)
    For Each mycell In RANG

        ' If the count of mycell in RANG is greater than 1, then set the value of the cell 1 across to the right of mycell (i.e. column G) as "Duplicate Found"
        If Application.WorksheetFunction.CountIf(RANG, mycell.Value) > 1 Then

        'how do i find the last row index of each duplicate here?

    Next mycell

End Sub

Answer 1

有很多种方法。下面代码(测试)中使用的字典对象。请添加工具->参考->微软脚本运行时。

Sub Testing()
    Dim mycell As Range, RANG As Range, Dict As Dictionary, Mname As String, Rng As Range
    Set Dict = New Dictionary
    With Sheets(1)
        ' Build a range (RANG) between cell F2 and the last cell in column F
        Set RANG = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
    End With


    ' For each cell (mycell) in this range (RANG)
    For Each mycell In RANG
        Mname = mycell.Value
        ' If the count of mycell in RANG is greater than 1, then set the value of the cell 1 across to the right of mycell (i.e. column G) as "Duplicate Found"
        If Application.WorksheetFunction.CountIf(RANG, mycell.Value) > 1 Then


        If Dict.Count > 0 And Dict.Exists(Mname) Then
        Dict(Mname) = mycell.Row()
        Else
        Dict.Add Mname, mycell.Row()
        End If

        End If
    Next mycell


    'Display result in debug window (Modify to your requirement)
    Startrow = 2
    For Each Key In Dict.Keys
    Set Rng = Sheets(1).Range("A" & Startrow & ":A" & Dict(Key))
    Startrow = Dict(Key) + 1
    ' Now may copy etc the range Rng
    Debug.Print Key, Dict(Key), Rng.Address
    Next



End Sub

修改以给出范围对象的代码(从注释中理解)

Answer 2

1. 创建一个下拉列表，其中包含b1 (通过数据验证)

中的所有名称

1. In c1粘贴以下函数(以显示最后一个值出现的行号)

{=MAX(($A$1:$A$26=$B$1)*ROW($A$A:$A$226))}