[已解决]怎么实现同行快速对比?

On_fire

怎么实现同行快速对比?

Data页, 是原始资料

Compare页,是模拟结果

代码需求: 是对比每一, 同行两列(ABCD四列), 共6个组合, 判断内容是否相同?

如果是相同=Y,不相同=N,  (如有空白,不处理).

因为数据N多, 超10W行, 请老师, 大神…帮帮忙, 谢谢…!

最佳答案

月排行榜 / 总排行榜

yanhj

2017-11-9 08:18

简化一下
Private Sub CommandButton1_Click()
'A与B、C、D比对
For i1 = 1 To Range("A65536").End(3).Row
   For j = 2 To 4
    If Cells(i1, 1) = Cells(i1, j) Then
     Cells(i1, 5 + j) = "Y"
    End If
    If Cells(i1, 1) <> Cells(i1, j) Then
     Cells(i1, 5 + j) = "N"
    End If
    If Cells(i1, 1) = "" Or Cells(i1, j) = "" Then
    Cells(i1, 5 + j) = ""
    End If
  Next
Next
  'B与C、D比对
For i1 = 1 To Range("A65536").End(3).Row
   For j = 3 To 4
      If Cells(i1, 2) = Cells(i1, j) Then
        Cells(i1, 7 + j) = "Y"
      End If
      If Cells(i1, 2) <> Cells(i1, j) Then
        Cells(i1, 7 + j) = "N"
      End If
      If Cells(i1, 2) = "" Or Cells(i1, j) = "" Then
        Cells(i1, 7 + j) = ""
      End If
    Next
Next

'C与D比对
For i1 = 1 To Range("A65536").End(3).Row
     If Cells(i1, 3) = Cells(i1, 4) Then
       Cells(i1, 12) = "Y"
     End If
     If Cells(i1, 3) <> Cells(i1, 4) Then
       Cells(i1, 12) = "N"
     End If
     If Cells(i1, 3) = "" Or Cells(i1, 4) = "" Then
       Cells(i1, 12) = ""
     End If
Next
End Sub

跳转到最佳答案楼层

yanhj

Private Sub CommandButton1_Click()
'A与B比对
For i1 = 1 To Range("A65536").End(3).Row
    If Cells(i1, 1) = Cells(i1, 2) Then
     Cells(i1, 7) = "Y"
    End If
    If Cells(i1, 1) <> Cells(i1, 2) Then
     Cells(i1, 7) = "N"
    End If
      
    If Cells(i1, 1) = "" Or Cells(i1, 2) = "" Then
    Cells(i1, 7) = ""
    End If
Next

'A与C比对
For i1 = 1 To Range("A65536").End(3).Row
     If Cells(i1, 1) = Cells(i1, 3) Then
     Cells(i1, 8) = "Y"
    End If
    If Cells(i1, 1) <> Cells(i1, 3) Then
     Cells(i1, 8) = "N"
    End If
      
    If Cells(i1, 1) = "" Or Cells(i1, 3) = "" Then
    Cells(i1, 8) = ""
    End If
Next

  'A与D比对
For i1 = 1 To Range("A65536").End(3).Row
     If Cells(i1, 1) = Cells(i1, 4) Then
      Cells(i1, 9) = "Y"
     End If
   
     If Cells(i1, 1) <> Cells(i1, 4) Then
      Cells(i1, 9) = "N"
     End If
      
     If Cells(i1, 1) = "" Or Cells(i1, 4) = "" Then
      Cells(i1, 9) = ""
     End If
Next

  'B与C比对
For i1 = 1 To Range("A65536").End(3).Row
     If Cells(i1, 2) = Cells(i1, 3) Then
      Cells(i1, 10) = "Y"
     End If
   
     If Cells(i1, 2) <> Cells(i1, 3) Then
      Cells(i1, 10) = "N"
     End If
      
     If Cells(i1, 2) = "" Or Cells(i1, 3) = "" Then
      Cells(i1, 10) = ""
    End If
Next

  'B与D比对
For i1 = 1 To Range("A65536").End(3).Row
     If Cells(i1, 2) = Cells(i1, 4) Then
      Cells(i1, 11) = "Y"
     End If
   
     If Cells(i1, 2) <> Cells(i1, 4) Then
      Cells(i1, 11) = "N"
     End If
      
     If Cells(i1, 2) = "" Or Cells(i1, 4) = "" Then
      Cells(i1, 11) = ""
    End If
Next

'C与D比对
For i1 = 1 To Range("A65536").End(3).Row
     If Cells(i1, 3) = Cells(i1, 4) Then
      Cells(i1, 12) = "Y"
     End If
   
     If Cells(i1, 3) <> Cells(i1, 4) Then
      Cells(i1, 12) = "N"
     End If
      
     If Cells(i1, 3) = "" Or Cells(i1, 4) = "" Then
      Cells(i1, 12) = ""
    End If
Next
End Sub

yanhj

简化一下
Private Sub CommandButton1_Click()
'A与B、C、D比对
For i1 = 1 To Range("A65536").End(3).Row
   For j = 2 To 4
    If Cells(i1, 1) = Cells(i1, j) Then
     Cells(i1, 5 + j) = "Y"
    End If
    If Cells(i1, 1) <> Cells(i1, j) Then
     Cells(i1, 5 + j) = "N"
    End If
    If Cells(i1, 1) = "" Or Cells(i1, j) = "" Then
    Cells(i1, 5 + j) = ""
    End If
  Next
Next
  'B与C、D比对
For i1 = 1 To Range("A65536").End(3).Row
   For j = 3 To 4
      If Cells(i1, 2) = Cells(i1, j) Then
        Cells(i1, 7 + j) = "Y"
      End If
      If Cells(i1, 2) <> Cells(i1, j) Then
        Cells(i1, 7 + j) = "N"
      End If
      If Cells(i1, 2) = "" Or Cells(i1, j) = "" Then
        Cells(i1, 7 + j) = ""
      End If
    Next
Next

'C与D比对
For i1 = 1 To Range("A65536").End(3).Row
     If Cells(i1, 3) = Cells(i1, 4) Then
       Cells(i1, 12) = "Y"
     End If
     If Cells(i1, 3) <> Cells(i1, 4) Then
       Cells(i1, 12) = "N"
     End If
     If Cells(i1, 3) = "" Or Cells(i1, 4) = "" Then
       Cells(i1, 12) = ""
     End If
Next
End Sub

On_fire

yanhj 发表于 2017-11-9 08:18
简化一下
Private Sub CommandButton1_Click()
'A与B、C、D比对

谢谢老师…

原来是这样, 简单就是美, 我一开始的思路好乱…