Bonjour,
une formule surement mais une macro aussi :
Sub CommunsEntreEtatetFiche()
' articles en commun dans les 2 listes
' affichage des communs dans la colonne A
Dim Ws1, Ws2 As Worksheet
Set Ws1 = Sheets("etat"): Set Ws2 = Sheets("fiche")
a = Ws1.Range("c2:c" & [c65000].End(xlUp).Row)
Set Mondico1 = CreateObject("Scripting.Dictionary")
For Each c In a
If Not Mondico1.Exists(c) Then Mondico1.Add c, c
Next c
b = Ws2.Range("c2:c" & [c65000].End(xlUp).Row)
Set mondico2 = CreateObject("Scripting.Dictionary")
For Each c In b
If Mondico1.Exists(c) Then If Not mondico2.Exists(c) Then mondico2.Add c, c
Next c
Ws1.[a2].Resize(mondico2.Count, 1) = Application.Transpose(mondico2.Items)
Ws1.[A1].Value = "Communs"
End Sub