如何在 Excel 中查找具有重复列的行？

Question

我有一个很大的电子表格，其中可能有重复的行。但我只关心匹配下面以黄色突出显示的一些列。

所以我的问题是：如何才能在一行中的特定列中搜索重复项，并在找到匹配项时突出显示它们？

Answer 1

根据您提供的屏幕截图，这正是您想要的

前

当我点击“突出显示重复项”按钮后

顶部是您可以自定义的部分。目前，我正在查看 A 列和 B 列，但您可能希望将其更新为查看 B 列和 C 列或 A 列和 D 列等。

我还提供了影响突出显示颜色的详细信息和链接（再次参见代码中的注释）

Sub HighlightDuplicates()

    Dim transparent As Integer
    transparent = -4142 

    Dim yellow As Integer
    yellow = 27 ' colour index, see http://dmcritchie.mvps.org/excel/colors.htm for more details about setting the colour

    Dim column1 As String
    column1 = "A" 'Update me if you don't want to check for dupes in the A column

    Dim column2 As String
    column2 = "B" 'Update me if you don't want to check for dupes in the B column

    Dim endOfRows As Boolean
    moreRows = True

    Dim currentCell As Integer
    currentCell = 0

    Do While (moreRows)

        currentCell = currentCell + 1

        Dim aValue As String
        Dim bValue As String

        aValue = Worksheets("Sheet1").Range(column1 & currentCell).Value
        bValue = Worksheets("Sheet1").Range(column2 & currentCell).Value

        'check it isn't already coloured
        If (Worksheets("Sheet1").Range(column1 & currentCell).Interior.ColorIndex = transparent) Then

            Dim moreInnerRows As Boolean
            moreInnerRows = True

            Dim currentInnerCell As Integer
            currentInnerCell = currentCell

            Dim isDupe As Boolean
            isDupe = False
            'Now to loop through the other rows

            Do While (moreInnerRows)
                currentInnerCell = currentInnerCell + 1

                If (Worksheets("Sheet1").Range(column1 & currentInnerCell).Value = "" And Worksheets("Sheet1").Range(column2 & currentInnerCell).Value = "") Then
                    Exit Do
                End If

                If Worksheets("Sheet1").Range(column1 & currentInnerCell).Value = aValue And Worksheets("Sheet1").Range(column2 & currentInnerCell).Value = bValue Then
                    isDupe = True
                    Worksheets("Sheet1").Range(column1 & currentInnerCell).Interior.ColorIndex = yellow
                    Worksheets("Sheet1").Range(column2 & currentInnerCell).Interior.ColorIndex = yellow

                End If

            Loop

            If (isDupe = True) Then
                'Now we mark the original row as a dupe
                Worksheets("Sheet1").Range(column1 & currentCell).Interior.ColorIndex = yellow
                Worksheets("Sheet1").Range(column2 & currentCell).Interior.ColorIndex = yellow

            End If

        End If

        If (Worksheets("Sheet1").Range(column1 & currentCell).Value = "" And Worksheets("Sheet1").Range(column2 & currentCell).Value = "") Then
            Exit Do
        End If


    Loop

End Sub

Answer 2

我将添加一个新列并使用 CONCATENATE 列合并第 2 列和第 3 列。如果您的第一个单元格是 A1，则公式为：

=CONCATENATE(B1," ",C1)

然后从主页功能区转到条件格式/突出显示单元格规则/重复值。

这是最终结果的图片：