Kod:
Sub KimlikEslestir()
Dim GenelListe As Worksheet
Dim Birlestirme As Worksheet
Dim GenelListeRow As Range
Dim BirlestirmeRow As Range
Dim TC As String
Dim AdSoyad As String
Dim i As Long, j As Long
Set GenelListe = ThisWorkbook.Sheets("Genel Liste")
Set Birlestirme = ThisWorkbook.Sheets("Birleştirme")
For i = 2 To GenelListe.Cells(Rows.Count, 2).End(xlUp).Row
TC = GenelListe.Cells(i, 2).Value
AdSoyad = GenelListe.Cells(i, 3).Value
TCF = Left(TC, 2) & "*******" & Right(TC, 2)
AdSoyadF = Left(AdSoyad, 2) & "*"
For j = 2 To Birlestirme.Cells(Rows.Count, 1).End(xlUp).Row
If Birlestirme.Cells(j, 1).Value = TCF And Left((Birlestirme.Cells(j, 2).Value), 3) = AdSoyadF Then
Cells(j, 3).Value = TC
Cells(j, 4).Value = AdSoyad
Exit For
End If
Next j
Next i
End Sub