XL 2019 Liste déroulante sans vide avec données de plusieurs plages

[Freedal]

Bonjour à tous
J'ai établi une liste déroulante sans vide avec des données issues d'une même plage mais je n'arrive pas à faire de même avec des données issues de plages différentes.
Merci d'avance.

 

[JHA]

Bonjour à tous,

Un essai en pièce jointe

JHA

 

[Freedal]

Bonjour JHA ,
Désolé c'est une piste mais reste quelques problémes.
Merci

 

[job75]

Bonjour Freedal, JHA,

Une solution avec cette fonction VBA :

Function Liste(r As Range)
Dim d As Object, x$, a
Set d = CreateObject("Scripting.Dictionary")
For Each r In r
    x = Application.Proper(Trim(CStr(r)))
    If x <> "" Then d(x) = "" 'liste sans doublon
Next
a = d.keys
tri a, 0, UBound(a)
'---restitution---
Liste = a 'vecteur horizontal
End Function

Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
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

Formule en J4 =SIERREUR(INDEX(Liste((H$4:H$9;H$13:H$18;H$22:H$27));;LIGNE()-3);"")

Edit : retiré d.CompareMode car la casse est forcément ignorée.

A+

 

[Freedal]

Merci Job75, c'est du grand art
ça fonctionne très bien, est-il possible de garder la liste dans le même ordre que dans la plage ?
Ta solution fait que la liste est dans l'ordre alphabétique.
Donc obligé de régler le problème par une macro, j'avais tout essayé en modifiant ma solution de la partie gauche qui fonctionne.
Merci encore

 

[job75]

Si vous ne voulez pas de tri neutralisez la ligne de code où il se fait !!!

Le VBA n'est pas indispensable, on peut obtenir le résultat par formule en traitant la plage H4:H27 avec un test pour éliminer les cellules contenant "Données", voyez le fichier joint et cette formule matricielle en J4 :

=SIERREUR(INDEX(H:H;PETITE.VALEUR(SI((H$4:H$27<>"")*ESTERREUR(CHERCHE("Données";H$4:H$27));LIGNE(H$4:H$27));LIGNE()-3));"")

 

[Freedal]

Merci job75, je vais étudier cela.
Toujours frustré quand j'obtiens de l'aide de ne pouvoir rendre.
Un grand merci job75