excel - 在两张纸的两列中查找重复项

Question

我需要比较两张不同工作表中的两列并找出重复项:将“Sheet2”中的 C 列与“Sheet1”中的 E 列进行比较。作为示例，我只使用了几个，但是:

Column E in Sheet1 has 2,000 rows of data.  
Column C in Sheet2 has ~ 100 rows of data.

表格截图: http://postimg.org/image/jtuinkqgz/

表 1


表2


链接到 sample Excel file .

Option Explicit
Sub CompareColumns()
'---------------------------------------------------------------------------------------------------
This module loops through two columns in Excel and identifies items without a match. The columns can be on different sheets. -----------
'---------------------------------------------------------------------------------------------------
Dim strCol1 As String 'First Column Location
Dim strCol2 As String 'Second Column Location
Dim strColResults As String 'Output Column
Dim strSheetname1 As String 'First sheet name
Dim strSheetname2 As String 'Second sheet name
Dim iListStart As Integer 'Row where List Begins
Dim strTemp As String
Dim i As Integer, j As Integer
Dim iLastRow1 As Integer, iLastRow2 As Integer

'---Edit these variables---'
strSheetname1 = "Sheet1"
strSheetname2 = "Sheet2"
strCol1 = "A"
strCol2 = "C"
'strColResults = "B"
iListStart = 1
'--------------------------'

iLastRow1 = Sheets(strSheetname1).Range(strCol1 & "50000").End(xlUp).Row
iLastRow2 = Sheets(strSheetname2).Range(strCol2 & "50000").End(xlUp).Row

'error check
If iListStart > WorksheetFunction.Min(iLastRow1, iLastRow2) Then
MsgBox ("List not found. Perform logic check on input variables.")
Exit Sub
End If

Sheets(strSheetname1).Range(strCol1 & iListStart & ":" & strCol1 & iLastRow1).Interior.ColorIndex = 0
Sheets(strSheetname2).Range(strCol2 & iListStart & ":" & strCol2 & iLastRow2).Interior.ColorIndex = 0

strTemp = "<<"
If iLastRow2 > iLastRow1 Then 'switch the order
strTemp = strCol1
strCol1 = strCol2
strCol2 = strTemp
strTemp = strSheetname1
strSheetname1 = strSheetname2
strSheetname2 = strTemp
strTemp = ">>"
End If

'Identify unmatched items in long column
For i = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
For j = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
If UCase(Sheets(strSheetname2).Range(strCol2 & j)) =          UCase(Sheets(strSheetname1).Range(strCol1 & i)) Then
'Range(strColResults & i) = i & " to " & j
Exit For ' Stops at first match
ElseIf j = WorksheetFunction.Min(iLastRow1, iLastRow2) Then
'Range(strColResults & i) = strTemp
Sheets(strSheetname1).Range(strCol1 & i).Interior.Color = 255
End If
Next j
Next i

'Identify unmatched items in short column
If strTemp = "<<" Then
strTemp = " >>"
Else
strTemp = " <<"
End If

For i = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
For j = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
If UCase(Sheets(strSheetname1).Range(strCol1 & j)) =             UCase(Sheets(strSheetname2).Range(strCol2 & i)) Then
Exit For
ElseIf j = WorksheetFunction.Max(iLastRow1, iLastRow2) Then
'Range(strColResults & i) = Range(strColResults & i) & strTemp
Sheets(strSheetname2).Range(strCol2 & i).Interior.Color = 255
End If
Next j
Next i
End Sub

如何在 C 列中找到重复项，并可能将 Sheet2 的 C 列的重复项与 Sheet1 的 E 列着色？

Answer 1

似乎不是这个 OP 想要的，但对于具有非常相似要求的其他人来说似乎是一个不错的选择:在 Sheet2 和 HOME > Styles - Conditional Formatting, New Rule... 中选择 ColumnC，使用公式来确定要格式化的单元格和在此公式为真时格式化值: :

=COUNTIF(Sheet1!E:E,C1)

格式...，选择您选择的格式，OK，OK。