1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
| Sub fusion()
Set dico = CreateObject("Scripting.Dictionary")
Set f1 = Sheets("data1")
Set f2 = Sheets("data2")
Set f3 = Sheets("data3")
Set f4 = Sheets("data4")
Set f5 = Sheets("data5")
A = f1.Range("A2:J" & f1.[a650000].End(xlUp).Row)
B = f2.Range("A3:J" & f2.[a650000].End(xlUp).Row)
C = f3.Range("A3:T" & f3.[a650000].End(xlUp).Row)
D = f4.Range("A3:I" & f4.[a650000].End(xlUp).Row)
G = f5.Range("A3:I" & f5.[a650000].End(xlUp).Row)
n = UBound(A) + UBound(B) + UBound(C) + UBound(D) + UBound(G)
Dim e: ReDim e(1 To n, 1 To 26)
m = 0
For i = LBound(A) To UBound(A)
crit = A(i, 1) & " " & A(i, 2) & " " & A(i, 3) & " " & A(i, 4) & " " & A(i, 5) & " " & A(i, 6) & " " & A(i, 7) & " " & A(i, 8)
If Not dico.exists(crit) Then m = m + 1: dico(crit) = m: p = m Else p = dico(crit)
For k = 1 To 8
e(p, k) = A(i, k)
Next k
e(p, 9) = A(i, 9): e(p, 10) = A(i, 10)
Next i
For i = LBound(B) To UBound(B)
crit = B(i, 1) & " " & B(i, 2) & " " & B(i, 3) & " " & B(i, 4) & " " & B(i, 5) & " " & B(i, 6) & " " & B(i, 7) & " " & B(i, 8)
If Not dico.exists(crit) Then m = m + 1: dico(crit) = m: p = m Else p = dico(crit)
For k = 1 To 8
e(p, k) = B(i, k)
Next k
e(p, 11) = B(i, 9): e(p, 12) = B(i, 10)
Next i
For i = LBound(C) To UBound(C)
crit = C(i, 1) & " " & C(i, 2) & " " & C(i, 3) & " " & C(i, 4) & " " & C(i, 5) & " " & C(i, 6) & " " & C(i, 7) & " " & C(i, 8)
If Not dico.exists(crit) Then m = m + 1: dico(crit) = m: p = m Else p = dico(crit)
For k = 1 To 8
e(p, k) = C(i, k)
Next k
e(p, 13) = C(i, 20): e(p, 14) = C(i, 13): e(p, 15) = C(i, 9): e(p, 16) = C(i, 10): e(p, 17) = C(i, 11): e(p, 18) = C(i, 12): e(p, 19) = C(i, 14): e(p, 20) = C(i, 15): e(p, 21) = C(i, 16): e(p, 22) = C(i, 17): e(p, 23) = C(i, 18): e(p, 24) = C(i, 19)
Next i
For i = LBound(D) To UBound(D)
crit = D(i, 1) & " " & D(i, 2) & " " & D(i, 3) & " " & D(i, 4) & " " & D(i, 5) & " " & D(i, 6) & " " & D(i, 7) & " " & D(i, 8)
If Not dico.exists(crit) Then m = m + 1: dico(crit) = m: p = m Else p = dico(crit)
For k = 1 To 8
e(p, k) = D(i, k)
Next k
e(p, 25) = D(i, 9)
Next i
For i = LBound(G) To UBound(G)
crit = G(i, 1) & " " & G(i, 2) & " " & G(i, 3) & " " & G(i, 4) & " " & G(i, 5) & " " & G(i, 6) & " " & G(i, 7) & " " & G(i, 8)
If Not dico.exists(crit) Then m = m + 1: dico(crit) = m: p = m Else p = dico(crit)
For k = 1 To 8
e(p, k) = G(i, k)
Next k
e(p, 26) = D(i, 9)
Next i
Sheets("Fusion").[A2].Resize(dico.Count, UBound(e, 2)) = e
End Sub |
Partager