Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = 0
If Not Intersect(Target, Range("AQ3:BB65000")) Is Nothing Then
Range(Range("BC3"), Range("BC3").End(xlDown)) = ""
Dim Dico, i As Long, j As Byte
Set Dico = CreateObject("Scripting.Dictionary")
With Worksheets("Feuil1")
For j = 43 To 55 ' pour chaque colonne de AQ à BB
For i = 3 To .Cells(Cells.Rows.Count, j).End(xlUp).Row
Dico(CStr(.Cells(i, j))) = ""
Next
Next
.Cells(3, 55).Resize(Dico.Count, 1) = Application.Transpose(Dico.keys)
End With
Range("BC2:BC65000").Sort Range("BC2"), xlAscending, Header:=xlYes ' trier
End If
Application.ScreenUpdating = -1
End Sub
=sansdoublonstrié(AQ3:BB17)
Option Compare Text
Function SansDoublonsTrié(champ As Range)
Set mondico = CreateObject("Scripting.Dictionary")
mondico.CompareMode = vbTextCompare
temp = champ
For Each c In temp
If c <> "" Then mondico(c) = ""
Next c
Dim b()
ReDim b(1 To Application.Caller.Rows.Count)
i = 1
For Each c In mondico.keys
b(i) = c
i = i + 1
Next
Call tri(b, 1, mondico.Count)
SansDoublonsTrié = Application.Transpose(b)
End Function
Sub tri(a, gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
temp = a(g): a(g) = a(d): a(d) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub
=SIERREUR(INDIRECT(ADRESSE(MOD(PETITE.VALEUR(SI((B$3:M$17<>"")*(NB.SI(P$2:P2;B$3:M$17)=0);NB.SI(B$3:M$17;"<"&B$3:M$17)*10^5+LIGNE(B$3:M$17));1);10^5);MOD(MOD(PETITE.VALEUR(SI((B$3:M$17<>"")*(NB.SI(P$2:P2;B$3:M$17)=0);NB.SI(B$3:M$17;"<"&B$3:M$17)*10^5+LIGNE(B$3:M$17)*10^2+COLONNE(B$3:M$17));1);10^5);10^2)));"")