請問Excel的找出相異數值的方式

labee wrote:
如圖的左邊，想請教有...(恕刪)

labee wrote:
如圖的左邊，想請教有...(恕刪)

silentcat wrote:
樞紐分析拉一下一分鐘(恕刪)

               
Sheets("_" & n & "_").Columns.AutoFit '調整欄位大小用
'下面這2行自己決定要用那一個，同時用、都不用都行
'只留不同版本
Sheets("_" & n & "_").Range("B:B").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'刪掉條碼
Sheets("_" & n & "_").Range("c:c").Delete Shift:=xlToLeft

Sub autofilter()

    Dim c As String, allcriteria, lastrow As Double
    c = InputBox("請輸入英文欄位名稱，預設 a 欄", , "a")
    If c = "" Then Exit Sub
    
    On Error Resume Next
    Application.ScreenUpdating = False
    Sheets("總表").ShowAllData
    Sheets("總表").Cells.Clear
    Sheets("總表").Range("a1:c1") = Array("name", "ver", "code")
    
    With Sheets("sheet1")
    
        .Range(c & ":" & c).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Set allcriteria = .Range(.Cells(2, c), .Cells(2, c).End(xlDown)).SpecialCells(xlCellTypeVisible)
        lastrow = 2
        
        For Each n In allcriteria
               .Range(c & ":" & c).autofilter Field:=1, Criteria1:=n
'3 改成2 ，不複製條碼
               .autofilter.Range.Offset(1).Resize(, 3).Copy Sheets("總表").Cells(lastrow, 1)
               lastrow = Sheets("總表").Range("A1").CurrentRegion.Rows.Count + 1
        Next
        Sheets("總表").Columns.AutoFit
        .AutoFilterMode = False
    
    End With
    Application.ScreenUpdating = True
    
End Sub

Sub find_difference()

    Dim lastrow As Double, i As Integer, f As String
    Application.ScreenUpdating = False
    
    With Sheets("工作表1")
        
        lastrow = .Range("A1").CurrentRegion.Rows.Count
        f = "=SUMPRODUCT(--((R1C1:R" & lastrow & "C1=RC[-2])*(R1C2:R" & lastrow & "C2=RC[-1])))"
        
        .Range("f1").Resize(lastrow, 3).Delete Shift:=xlUp
        .Range("a1").Resize(lastrow, 2).Copy .Range("f1")
        .Range("f1").Resize(lastrow, 2).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
        .Range("h1") = "數量"
        
        lastrow = .Range("f1").CurrentRegion.Rows.Count
        For i = lastrow To 2 Step -1
            If WorksheetFunction.CountIf(Range("f1:f" & lastrow), .Cells(i, 6)) = 1 Then
                .Cells(i, 6).Resize(, 3).Delete Shift:=xlUp
            End If
        Next i
        
        lastrow = .Range("f1").CurrentRegion.Rows.Count
        .Range("h2:h" & lastrow).FormulaR1C1 = f
        
    End With

    Application.ScreenUpdating = True
    
End Sub