如何识别两列中具有相同值的行? [英] How to identify rows that have the same value in two columns?

问题描述

如果B列 AND 的C列使用VBA脚本具有重复的值，我正在尝试标识整行.例如，如果我有一个包含以下内容的表:

I'm trying to identify an entire row if column B AND column C have repeating values with a VBA script. For example if I have a table that contains:

ID   Age   Grade
1    14     90
2    15     78
3    14     90
4    16     86
5    16     86
6    15     89
7    14     88

运行脚本后，我想要一个新的工作表，其中C列的 AND 列C中的行具有重复的值.因此，新工作表应如下所示:

After I run the script, I want a new sheet with the rows that have repeating values in column B AND column C. So the new sheet would look like:

ID   Age   Grade
1    14     90
3    14     90
4    16     86
5    16     86

到目前为止，这是我确定行的方式.我还没走那么远.

This is what I have so far in terms of identifying the rows. I'm not that far along.

Sub ID()
Dim LastRowcheck As Long, n1 As Long
Dim LastRowcheck2 As Long, n2 As Long

With Worksheets("grades")
    LastRowcheck = .Range("B" & .Rows.Count).End(xlUp).Row
    LastRowcheck = .Range("C" & .Rows.Count).End(xlUp).Row

    For n1 = LastRowcheck To 1 Step -1
        If .Cells(n1, 1).Value = Cells(n1 + 1, 1).Value And .Cells(n2, 1).Value = Cells(n2 + 1, 1).Value Then
           '''export to new sheet
        End If
    Next n1
End With
End Sub

推荐答案

使用application.countifs标识倍数.

Dim lastRowcheck As Long, n1  As Long

With Worksheets("grades")
    lastRowcheck = Application.Max(.Range("B" & .Rows.Count).End(xlUp).Row, _
                                   .Range("C" & .Rows.Count).End(xlUp).Row)

    For n1 = lastRowcheck To 1 Step -1
        If Application.CountIfs(.Columns("B"), .Cells(n1, "B").Value2, .Columns("C"), .Cells(n1, "C").Value2) > 1 Then
           '''export to new sheet
           Debug.Print .Cells(n1, "A") & ":" & .Cells(n1, "B") & ":" & .Cells(n1, "C")
        End If
    Next n1
End With

这篇关于如何识别两列中具有相同值的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！