Supprimer les blancs dans une liste déroulante

[lucifer71]

Bonjour à tous,

Je suis à la recherche d'une formule (pas de macro svp ) qui me permette d'avoir une liste déroulante sans espace blanc.
J'ai bien testé la solution apporté sur cette page https://www.excel-downloads.com/threads/suppression-des-blancs-dans-liste-deroulante.31243/ mais je n'arrive pas à un résultat correct, et je pense que je fais une erreur quelque part ?

Pour expliquer brièvementm, je vous joint un fichier d'exemple :
En colonne A : mes données de base
En colonne B : les valeur que je souhaite dans ma liste déroulante
En collone C : lon champ de recherche (en jaune) qui ne permet d'afficher en colonne B que ce qui contient les caractères cherchés
En colonne D : ma liste déroulante résultante.

Mon problème : je souhaiterai n'avoir en colonne D (donc dans ma liste déroulante), que les valeur résultantes de ma colonne B.

Je tourne en rond avec ce problème, j'ai bien essayer de passer par des formules que j'ai pu trouver ça et là sur internet, mais rien n'y fait :-/

Est-ce que l'un d'entre vous pourais m'aider sur la marche à suivre ?
D'avance merci pour votre aide !

Cordialement

 

[JBOBO]

Re : Supprimer les blancs dans une liste déroulante

Bonjour,

Un essai avec 2 colonnes supplementaires (je ne sais pas faire autrement).

 

[lucifer71]

Re : Supprimer les blancs dans une liste déroulante

Super, merci pour cet exemple !!!!!!
Quelle réactivité !

J'ai bien pigé le principe de numérotation dans ta première colonne.
Par contre, je pige pas comment fonctionne la formule suivante ? Tu fais un décallage des cellules, c'est ça ? je pige pas de quelle façon ?

Seconde chose : ma liste contient les valeurs "non vides" et les valeurs vides à la suite. Est-ce qu'il serait possible de ne contenir QUE les valeurs, et pas les cases vides ?

Encore merci pour votre aide !

Cordialement

 

[JBOBO]

Re : Supprimer les blancs dans une liste déroulante

Bonjour,

Ci-joint fichier modifié avec qq explications sommaire !

 

[lucifer71]

Re : Supprimer les blancs dans une liste déroulante

C'est parfait !!!!

Je ne saurait comment te remercier sinon en te disant un grand grand merci !!!!

Vraiment super sympa de ta part (et en plus, j'ai compris comment ca marche, le pied ! )

Cordialement

 

[Monique]

Re : Supprimer les blancs dans une liste déroulante

Bonjour,

Avec des blancs et des doublons dans la liste d'origine

 

[lucifer71]

Re : Supprimer les blancs dans une liste déroulante

Bonjour à vous 2.

Encore un grand merci pour vos propositions.

2 recmarques cepandant.
Après avoir implémenté et testé vos solutions respectives dans mon fichiers il s'avère que :
- pour la solution de Jbobo, si la recherche de critère ne donne rien, la plage de renvoi n'est plus bonne, elle donne D2 : D1 comme résultat, donc celà génère une erreur.
- pour la solution de Monique, c'est vraiment top, MAIS, étant donné que ma recherche de critère se fait sur 5 bases de données d'environ 1000/2000 lignes chacune, le recalcul est vraiment très très long, à cause de la recherche matricielle je suppose.

J'essaye d'apporter les corrections nécessaires, mais j'avoue que je peine beaucoup sur la redéfinition de la plage pour la solution de Jbobo, et je ne vois pas comment réduire le temps de calcul (pour la solution de monique).

Merci encore à vous 2 pour votre aide.

Cordialement

 

[lucifer71]

Re : Supprimer les blancs dans une liste déroulante

Seconde chose concernant la solution de Jbobo : comment faire pour utiliser cette liste de validation dans une autre feuille ?
J'ai beau passer par une gestion par nom de la formule, à chaque fois ca me modifie le contenu de la formule, et j'avoue que je m'y perd un peu ? (j'ai pourtant essayé de comprendre le principe de nommage utilisé par monique dans son fichier... :/ )

 

[Monique]

Re : Supprimer les blancs dans une liste déroulante

Bonjour,

On peut supposer qu'il n'y a pas de blancs dans la liste ?
S'il n'y en n'a pas, on peut définir d'une autre manière la plage "Liste"
=DECALER('Feuil1 (2)'!$A$2;;;NBVAL(ColA))

On peut aussi nommer entièrement la formule
(sauf qu'elle refuse de se laisser nommer entièrement)

Elle est donc nommée en deux temps

LigneListe (pour la partie Min(Si()), qui donne le n° de ligne
=MIN(SI(ESTNUM(CHERCHE(Search;Liste));SI(NB.SI('Feuil1 (2)'!G$1:G20;Liste)=0;LIGNE(Liste))))

FormExtrac (c'est la formule en entier)
=INDEX(ColA;LigneListe)

Il faut la valider par ctrl, maj et entrée alors que la plupart des formules nommées ne sont à valider que par "Entrée"

 

[Monique]

Re : Supprimer les blancs dans une liste déroulante

Re,

La méthode de Jbobo mais
liste de validation dans une autre feuille
Index Equiv à la place de Recherchev
1 colonne en moins en feuille 1 (Index Equiv sait chercher à gauche, pas Recherchev)

Pour accélérer, il faudrait quand même nommer les plages (la liste d'origine et les n°)

NB - Mettre des mises en forme jusqu'à la ligne 65 536 alourdit les fichiers

 

[david84]

Re : Supprimer les blancs dans une liste déroulante

Bonsoir tout le monde,
une solution plus courte si présence d'un titre en A1 :

=INDEX(A$1:A$60;MIN(SI((ESTNUM(CHERCHE(H$2;A$2:A$51))*(NB.SI(D$1:D1;A$2:A$51)=0));LIGNE(A$2:A$51);LIGNE(A$2:A$51)+LIGNES(A$2:A$51))))&""

ou en reprenant les plages nommées :

=INDEX(ColA;MIN(SI((ESTNUM(CHERCHE(H$2;Liste))*(NB.SI(E$1:E1;Liste)=0));LIGNE(Liste);LIGNE(Liste)+LIGNES(Liste))))&""

En remplaçant la partie LIGNE(Liste) ou LIGNE(A$2:A$51) par 2, cela fonctionne aussi dans l'exemple présent mais je n'ai pas testé plus avant.
A+

 

[lucifer71]

Re : Supprimer les blancs dans une liste déroulante

Merci monique !!!

Je viens de tester les 2 solutions.
La première comme la seconde, bien évidement, fonctionnent très bien.

Préférence imédiate pour la seconde (celle de Jbobo modifiée), car elle est beaucoup plus légére : par recherche matricielle, le fichier est vraiment très très lourd, à cause des bases de données conséquentes.

J'ai donc opté pour la solution 2, qui est parfait !!!

Merci beaucoup pour le temps consacré ainsi que pour les explications !!!

Je vous souhaite une excellente journée.

 

[hoerwind]

Re : Supprimer les blancs dans une liste déroulante

Bonjour,

Le même principe que JBOBO (sans formules matricielles) mais avec une colonne intermédiaire en moins.

 

[lucifer71]

Re : Supprimer les blancs dans une liste déroulante

que de solutions !!!!

Merci beaucoup pour votre aide à tous !

C'est parfait tout ca, parfait !

 

[lucifer71]

Re : Supprimer les blancs dans une liste déroulante

Bonjour à tous.

Je viens à nouveau à la charge à propos de ma liste déroulante super top.

J'ai implimenté la solution (monique + Jbobo combiné) dans mon fichier, c'est parfait !

Je cherche maintenant à faire "un cran plus loin" : je voudrais que lorsque je trouve la correspondance exacte de ma recherche, au lieu d'avoir affiché une liste déroulante, j'ai affiché la valeur.

Le but final est le suivant :
Je fais une recherche de composé chimique, donc soit je trouve le composé exact et je le met, soit je ne trouve pas le composé exact, et j'affiche la liste déroulante de proposition (comme vous m'avez indiqué)

J'ai essayé de passer par une formule conditionelle avec un "indirect", mais à chaque fois, je me retrouve avec une liste déroulante, et je dois faire un choix sur 1 valeur.
Ce que j'aimerai, c'est que je n'ai pas à cliquer et à sélectionner dans une liste de 1 item.

Avec bien entendu la contrainte : pas de macro.

Merci à tous par avance pour votre aide.

Cordialement