Update..
17.03.2022 10:28:39
UweD
Sub Vergleich()
Dim TB1 As Worksheet, TB2 As Worksheet, TB3 As Worksheet, LR3 As Long, LR As Long
Dim SP As Integer, Z1 As Integer
Set TB1 = Sheets("Liste 1")
Set TB2 = Sheets("Liste 2")
Set TB3 = Sheets("Wunsch")
SP = 1 'Spalte A
Z1 = 2 'wegen ggf Überschrift
LR = TB1.Cells(TB1.Rows.Count, SP).End(xlUp).Row 'letzte Zeile der Spalte
With TB3
.UsedRange.Delete
.Cells(1, SP) = TB1.Cells(1, SP) 'Überschrift
.Cells(1, SP + 1) = TB1.Name
.Cells(1, SP + 2) = TB2.Name
.Cells(1, SP + 3) = "Fehlt"
TB1.Cells(Z1, SP).Resize(LR - Z1 + 1).Copy .Cells(2, SP)
LR3 = .Cells(.Rows.Count, SP).End(xlUp).Row
LR = TB2.Cells(TB2.Rows.Count, SP).End(xlUp).Row
TB2.Cells(Z1, SP).Resize(LR).Copy .Cells(LR3 + 1, SP)
.Columns(SP).RemoveDuplicates Columns:=1, Header:=xlYes
LR3 = .Cells(.Rows.Count, SP).End(xlUp).Row
.Cells(2, SP + 1).Resize(LR3 - 1, 1).FormulaR1C1 = "=COUNTIF('" & TB1.Name & "'!C1,RC1)"
.Cells(2, SP + 2).Resize(LR3 - 1, 1).FormulaR1C1 = "=COUNTIF('" & TB2.Name & "'!C1,RC1)"
.Cells(2, SP + 3).Resize(LR3 - 1, 1).FormulaR1C1 = "=IF(RC[-2]+RC[-1]2,""Fehlt"","""")"
End With
End Sub
LG UweD