Option Explicit
Sub test()
Dim P1 As String, P2 As String, Sql As String
With Sheets("Feuil2")
P1 = Replace(Range(.Range("A3"), .Cells(.Cells.Rows.Count, "C").End(xlUp)).Address, "$", "")
P2 = Replace(Range(.Range("E3"), .Cells(.Cells.Rows.Count, "G").End(xlUp)).Address, "$", "")
End With
With CreateObject("Adodb.connection")
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=no;"""
Sql = "select [F1],[F2],[F3] from"
Sql = Sql & "( select * from [Feuil2$" & P1 & "]"
Sql = Sql & " union all"
Sql = Sql & " select * from [Feuil2$" & P2 & "]) "
Sql = Sql & " Group By [F1],[F2],[F3]"
Sql = Sql & " Having count([F1])=1"
Sheets("Feuil2").Range("I3").CopyFromRecordset .Execute(Sql)
.Close
End With
End Sub
=SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(NON(NB.SI(E$3:E$1000;A$3:A$1000))*(A$3:A$1000<>"");LIGNE(A$3:A$1000));LIGNES(I$3:I3)));"")
Intéressant!!Bonjour,
Un essai, source : http://boisgontierjacques.free.fr/fichiers/Divers/Dictionary.xls .
Cordialement
MerciBonjour,
VB:Option Explicit Sub test() Dim P1 As String, P2 As String, Sql As String With Sheets("Feuil2") P1 = Replace(Range(.Range("A3"), .Cells(.Cells.Rows.Count, "C").End(xlUp)).Address, "$", "") P2 = Replace(Range(.Range("E3"), .Cells(.Cells.Rows.Count, "G").End(xlUp)).Address, "$", "") End With With CreateObject("Adodb.connection") .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=no;""" Sql = "select [F1],[F2],[F3] from" Sql = Sql & "( select * from [Feuil2$" & P1 & "]" Sql = Sql & " union all" Sql = Sql & " select * from [Feuil2$" & P2 & "]) " Sql = Sql & " Group By [F1],[F2],[F3]" Sql = Sql & " Having count([F1])=1" Sheets("Feuil2").Range("I3").CopyFromRecordset .Execute(Sql) .Close End With End Sub