XL 2019 Validation de Données sur plusieurs colonnes

[Caninge]

Bonjour à tous,

Je n'arrive pas à trouver la solution.
Je viens donc vous demander naturellement la réponse.
Dans mon vrai tableau j'ai plusieurs colonnes de produits : Colonne B : AQ. Les colonnes ne sont pas identiques en nombre de lignes.
Dans mon exemple seulement 3.
Le but est de réunir ces 3 colonnes dans une seul colonne et dans l'ordre alphabétique.
Ensuite nommer la plage pour l'utiliser avec Validation de Données.
Mais voilà je coince. Pouvez-vous m'aider ?
Merci
CANINGE

 

[Dan]

Bonjour,
De mon coté je vois 4 colonnes ...
Par formule ce serait possible mais très long.
Par VBA, mais avant de vous proposer quelque chose qui vous convient, êtes-vous d'accord sur le principe d'utiliser VBA ?
Si oui où allez vous placez la liste de validation ? en H1 ? ou ailleurs

Cordialement

Edit : avec VBA
- Deplacez la liste de validation en H1
- Allez dans la formule ListeProduits
- Mettez cette formule --> =DECALER(Feuil1!$F$3;;;NBVAL(Feuil1!$F:$F))
- Allez ensuite dans l'éditeur VBA
- Insérer un module et collez le code ci-dessous
- Pour l'exécuter, cliquez sur Test puis appuyez sur la touche F5 (ou FN + F5)

Sub test()
Dim i As Byte
Dim tablo()
Dim dlg As Byte

For i = 2 To 5
    tablo = Range(Cells(3, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Value
    dlg = Cells(Rows.Count, 6).End(xlUp).Row + 1
    If dlg < 3 Then dlg = 3
    Cells(dlg, 6).Resize(UBound(tablo)) = tablo
Next i
End Sub

 

[Eric KERGRESSE]

Bonjour,

Il va falloir vous mettre à power Query...

 

[mapomme]

Bonjour à tous,

Je ne sais pas si Excel 2019 le permet (je ne crois pas) ?

Mais avec O365, la formule suivante le fait : =TRIER(DANSCOL(B3:E24;1))

 

[Eric KERGRESSE]

Bonjour Mapomme,

Super ta solution.

 

[Cousinhub]

Bonsoir,

Il va falloir vous mettre à power Query...

Entièrement d'accord
Mais c'est pas encore dans les us et coutumes, mais ça viendra, du moins, on l'espère...
Une autre solution via PQ, sans tableaux structurés en origine.
Bonne soirée

 

[mapomme]

Bonsoir @Caninge, @Dan, @Eric KERGRESSE, @Cousinhub,

Autre solution VBA : Cliquer sur le bouton Hop!

J'ai aussi changé la définition de ListeProduits :
=DECALER(Feuil1!$F$3:$F$9999;0;0;NBVAL(Feuil1!$F$3:$F$9999);1)

Le code est dans le module de Feuil1.

Sub ListeValidation()
Dim j&
   Range("f3:f" & Rows.Count).Clear
   Range(Cells(3, "b"), Cells(Rows.Count, "b").End(xlUp)).Copy Cells(3, "f")
   For j = [c1].Column To [e1].Column
      Range(Cells(3, j), Cells(Rows.Count, j).End(xlUp)).Copy Cells(Rows.Count, "f").End(xlUp).Offset(1)
   Next j
   Range(Cells(3, j), Cells(Rows.Count, j).End(xlUp)).Sort key1:=[f3], order1:=xlAscending, MatchCase:=False, Header:=xlNo
   With Range(Cells(3, j), Cells(Rows.Count, j).End(xlUp))
      .ClearFormats: .Borders.LineStyle = xlContinuous: .Interior.Color = RGB(230, 250, 230)
   End With
End Sub

 

[Caninge]

Bonjour à tous,
merci pour toutes ces réponses.
Je vais regarder à tête reposé et je vous tiens au courant.
CANINGE

 

[riton00]

Bonsoir,
Autre avec une formule matricielle
Slts

 

[Caninge]

Bonjour,

j'ai essayé de mettre vos solutions avec de plus ou moins de succès. Pas facile.
Mais comme mon fichier est déjà gros et qu'il peine à s'ouvrir le fait de rajouter des formules cela a empiré les choses. Sur une des pages il y a 7140 lignes.
Ce fichier regroupe toutes mes dépenses alimentaires depuis juin 2016. Quand on sait le budget qu'il faut pour manger il n'est pas étonnant que le fichier soit énorme. Il y a peut-être une autre façon de faire autrement, je ne sais pas.
CANINGE

 

[Dan]

Bonjour,

Je ne sais pas si vous avez testé ma solution mais si oui, qu'avez-vous comme souci ?

 

[Caninge]

Bonjour à tous, bonjour Dan.
J'ai réussi à réduire le fichier et il pourra être joint.
Les plages à réunir sont sur la feuille PRODUIT et la plage se nomme également PRODUITS.
La validation de données se trouve dans la cellule E3 de la plage OBSERVATION.
En fait il ne faut peut-être pas changer ou modifier la validation de données.
Il faut simplement si j'ose dire rassembler toutes les colonnes de la plage PRODUITS dans l'ordre alphabétique.
Bon courage er merci.

 

[Dan]

Vu que la colonne AQ est vide, c'est que vous voulez rassembler la liste ?

 

[Caninge]

Oui je l'avais mise en plus en cas de rajout d'un produit. On peut la nommer X par exemple en AQ 24 si cela rend la tâche plus simple.

 

[oguruma]

Bonjour,
Un début suite à la livraison de ton fichier d'essai. Proposition de la constitution d'un référentiel des produits par catégories.
Pour cela il faut transformer ton tableau

en tableaux structuré comme le monte l'image écran.
Puis Pwq arrive à ton secours en dépivotant les colonnes en lignes pour arriver à ceci

et ainsi le gros avantage tu peux faire ensuite des listes, sous-listes, requêtes etc. par catégories etc etc faire des filtres, bref tu as des listes de validation à tiroir

let
    Source = Excel.CurrentWorkbook(){[Name="TB_REFERENCES_PRODUITS"]}[Content],
    UNPIVOT = Table.UnpivotOtherColumns(Source, {}, "Attribut", "Valeur"),
    REN_COLONNES = Table.RenameColumns(UNPIVOT,{{"Valeur", "PRODUIT"}, {"Attribut", "CATEGORIE"}}),
    REORG_COLONNES = Table.ReorderColumns(REN_COLONNES,{"PRODUIT", "CATEGORIE"}),
    TRI_PAR_PRODUIT = Table.Sort(REORG_COLONNES,{{"PRODUIT", Order.Ascending}})
in
    TRI_PAR_PRODUIT

Juste une suggestion : quand cela est possible utiliser les tableaux structurés sans modération bien souvent c'est source de solutions