Liste dans l'ordre alphabétique extraite d'un tableau

[CISCO]

Bonjour

Histoire d'améliorer un fichier sur ce forum, et pour en apprendre un peu plus, pourriez vous me donner un petit coup de main ?

Sur une feuille, j'ai un tableau comportant plein de noms, avec des doublons, et des cellules vides. Comment faire pour en extraire la liste des noms, sans répétition, sans espace vide, placée dans une colonne ?

Je sais faire cela lorsque le tableau ne comporte qu'une colonne (il y a de nombreux exemples de formules sur ce forum). Je sais aussi le faire à partir d'un tableau en passant par une ou des colonnes intermédiaires. Mais peut on le faire directement, sans colonne intermédiaire ?

Merci d'avance

@ plus

EDIT : Même pas dans l'ordre alphabétique, mais toujours sans colonne intermédiaire, ça serait déja pas mal

 

[job75]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour CISCO, le forum,

Par formule

Mais par macro

Sub Liste()
Dim cel As Range, ref As Range
For Each cel In Range("A3:G20").SpecialCells(xlCellTypeConstants, 2)
Set ref = Range("I65536").End(xlUp)(2)
If Application.CountIf(Range("I3", ref), cel) = 0 Then ref = cel
Next
Range("I3", ref).Sort Key1:=Range("I3"), Order1:=xlAscending, Header:=xlNo
End Sub

A+

 

[youky(BJ)]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour CISCO,
Petite macro...

Sub Essai()
Set MonDico = CreateObject("Scripting.Dictionary")
lg = 3
For Each c In [A3:G20]
If Not MonDico.Exists(c.Value) And c.Value <> "" Then
MonDico.Add c.Value, c.Value
Cells(lg, "I") = c.Value: lg = lg + 1
End If
Next c
End Sub

 

[BOISGONTIER]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour,

Avec UDF

JB
Formation Excel VBA JB

 

[JNP]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour le forum ,
Youky : tes données ne sont pas triées.
une autre macro

Sub TriCisco()
Range("I3:I" & Range("I65536").End(xlUp).Row).ClearContents
Dim Plage As Range
Set Plage = Range("A3:G20")
Dim Cellule As Range, Tableau() As String, I As Integer
ReDim Tableau(Plage.Count - 1)
I = 0
For Each Cellule In Plage
    If Trim(Cellule) = "" Then
        ReDim Preserve Tableau(UBound(Tableau) - 1)
    Else
        Tableau(I) = Trim(Cellule)
        I = I + 1
    End If
Next
Dim Temporaire As String, J As Integer, K As Integer
For I = LBound(Tableau) To UBound(Tableau)
    J = I
    For K = J + 1 To UBound(Tableau)
        If Tableau(K) <= Tableau(J) Then J = K
    Next K
    If I <> J Then
        Temporaire = Tableau(J): Tableau(J) = Tableau(I): Tableau(I) = Temporaire
    End If
Next I
J = 3
For I = LBound(Tableau) To UBound(Tableau)
    If Tableau(I) <> Cells(J - 1, 9) Then
        Cells(J, 9) = Tableau(I)
        J = J + 1
    End If
Next I
End Sub

Par contre, j'étais parti sur une fonction matricielle, mais ça me donnait des résultats abérents ... Je vais me pencher sur celle de Jacques, je comprendrai peut-être où est mon problème...
Bonne journée

 

[JNP]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Re ,
En regardant de près, j'ai vu que Jacques utilisais Application.transpose. Quelqu'un pourrait-il m'en dire plus sur cette fonction ?
Je l'ai utilisée et après quelques bidouillages, ma fonction marche, mais au delà des résultats, elle me donne #N/A . Pourquoi ?

Function TriCisco(Plage As Range)
Dim Cellule As Range, Tableau() As String, I As Integer
ReDim Tableau(Plage.Count - 1)
I = 0
For Each Cellule In Plage
    If Trim(Cellule) = "" Then
        ReDim Preserve Tableau(UBound(Tableau) - 1)
    Else
        Tableau(I) = Trim(Cellule)
        I = I + 1
    End If
Next
Dim Temporaire As String, J As Integer, K As Integer
For I = LBound(Tableau) To UBound(Tableau)
    J = I
    For K = J + 1 To UBound(Tableau)
        If Tableau(K) <= Tableau(J) Then J = K
    Next K
    If I <> J Then
        Temporaire = Tableau(J): Tableau(J) = Tableau(I): Tableau(I) = Temporaire
    End If
Next I
For I = LBound(Tableau) To UBound(Tableau) - 1
    If Tableau(I) = Tableau(I + 1) Then
        Tableau(I) = "ZZZ"
    End If
Next I
For I = LBound(Tableau) To UBound(Tableau)
    J = I
    For K = J + 1 To UBound(Tableau)
        If Tableau(K) <= Tableau(J) Then J = K
    Next K
    If I <> J Then
        Temporaire = Tableau(J): Tableau(J) = Tableau(I): Tableau(I) = Temporaire
    End If
Next I
For I = UBound(Tableau) To LBound(Tableau) Step -1
    If Tableau(I) = "ZZZ" Then
        ReDim Preserve Tableau(UBound(Tableau) - 1)
    End If
Next I
TriCisco = Application.Transpose(Tableau)
End Function

Bonne journée

 

[job75]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Re,

JNP, la fonction transpose intervertit lignes et colonnes (un tableau vertical devient horizontal).

les #N/A s'affichent si on la transfère sur une plage plus grande. Par exemple le code :

Range("B1:E1").Value = Application.Transpose(Range("A1:A4"))

est OK, mais celui-ci ne l'est pas :

Range("B1:F1").Value = Application.Transpose(Range("A1:A4"))

car dimension 5 au lieu de 4.

A+

 

[JNP]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Re ,
Merci Job75 de tes réponses.
J'en conclu donc qu'une variable tableau à 1 dimension est toujours horizontale, et si elle a une 2ème dimension, ce sera la verticale. La 3ème ne sera pas renvoyée dans la feuille.
Par contre, pour le transpose, ça veux dire que le tableau renvoyé par Jacques est plus grand que ce qui est nécessaire (j'ai fait un test en redimensionnant mon tableau et effectivement, ça supprime les #N/A). Mais c'est un peu stupide, vu que les variables tableau vont consommer plus de mémoire... Enfin, il suffit d'éliminer par formule le résultat en erreur, mais bon, c'est dommage qu'on ne puisse pas le faire proprement dans la fonction.
Encore merci et bonne journée

 

[MJ13]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour à tous

Sujet très intéressant.

Je vous ai fait un fichier avec les solutions en VBA (je n'ai pas inclu celle de JB ou de JNP qui est sous forme de matricielle et fonction, mais j'ai inclu la solution de JNP en VBA).

On trouve des différences de temps de calcul impressionnant surtout avec une zone longue a extraire (faites le test avant de faire CopieZOne et après).

Bravo aussi à Youky qui remporte la palme d'Excel.

 

[CISCO]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bnsoir à tous

Et merci

Me reste plus qu'à me mettre au VBA

Je teste tout çà avec mes petits neurones, et j'essaye d'y comprendre quelques choses dès que possible.

@ plus

 

[Monique]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour,

Sans macro, sans colonne intermédiaire mais sans ordre alphabétique

 

[MJ13]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour Monique


Merci pour cette très belle démonstration qui j'en suis sur marquera les mémoires d'XLD.

C'est la que je comprend que les formules c'est vraiment pas mon truc.

Bon Wek-end.

 

[job75]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour le fil,

@ MJ13 : toujours intéressant en effet de comparer les temps de calcul.

Je n'ai jamais testé avec une New Collection, peux-tu Michel faire le test avec cette macro (écrite pour le 1er fichier de CISCO) :

Sub Liste()
'Job75 avec collection
Dim cel As Range, c As New Collection, i As Long
Range("I3:I65536").ClearContents
On Error Resume Next
For Each cel In Range("A3:G65536").SpecialCells(xlCellTypeConstants, 2)
c.Add cel, cel
Next
For i = 1 To c.Count
Range("I" & i + 2) = c(i)
Next
Range("I3:I65536").Sort Key1:=Range("I3"), Order1:=xlAscending, Header:=xlNo
End Sub

Merci d'avance.

EDITION 1 : j'ai testé sur le fichier de MJ13 : 12 secondes, c'est nettement moins rapide qu'avec le CreateObject de youky (4 à 5 secondes).

EDITION 2 : cela me paraissant bizarre, j'ai retesté en mode pas à pas et constaté que la ligne :

For Each cel In Range("A3:G65536").SpecialCells(xlCellTypeConstants, 2)

prend un temps de 8 secondes ! (Elle n'est exécutée qu'une fois).

CONCLUSION : mieux faut tester dans la plage entière comme youky. Le temps total est réduit à 2 secondes avec :

For Each cel In [A3:G65536] 'A2:D65536 dans le fichier de MJ13
If cel <> "" Then c.Add cel, cel
Next

A+

 

[CISCO]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjour,

Sans macro, sans colonne intermédiaire mais sans ordre alphabétique

Vu ce que tu proposes d'habitude, je me doutais bien que si tu passais par là, tu trouverais une solution... Vraiment, chapeau... Pour le moment, je suis loin d'avoir les idées assez claires (sous le chapeau) dans ce domaine pour y arriver. Je reconnais la méthode que tu as utilisée, déja vu sur d'autres posts pour un tableau ne comportant qu'une seule colonne, mais pour passer à un tableau de plusieurs colonnes.

Le "pire", c'est que ce qui est difficile, ce n'est pas de faire (faire) la liste des noms du tableau (cf pièce jointe), c'est d'éliminer les doublons et les blancs dans la même formule...

Je te remercie encore, ainsi que tous les autres intervenants précédents.

Bon W.E.

 

[MJ13]

Re : Liste dans l'ordre alphabétique extraite d'un tableau

Bonjoour à tous

Je n'ai jamais testé avec une New Collection, peux-tu Michel faire le test avec cette macro (écrite pour le 1er fichier de CISCO) :

Job j'ai testé avec New Collection

En effet c'est beaucoup plus rapide, mais bon il faut le savoir.

Merci pour l'astuce.

Ah ces définitions de variables!

Bon Week-end à tous.