XL 2019 Validation de Données sur plusieurs colonnes

Dan

XLDnaute Barbatruc

• 7 Décembre 2023

• #2

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)

Code:

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

 

Pièces jointes

• Validation de Données sur 3 Colonnes.xlsm

  15.8 KB · Affichages: 3

Dernière édition: 7 Décembre 2023

Vote positif 0 Vote négatif

E

Eric KERGRESSE

XLDnaute Occasionnel

• 7 Décembre 2023

• #3

Bonjour,

Il va falloir vous mettre à power Query... 😊

 

Pièces jointes

• Caninge Validation de Données sur 3 Colonnes.xlsx

  23.8 KB · Affichages: 9

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 7 Décembre 2023

• #4

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))

 

Vote positif 1 Vote négatif

E

Eric KERGRESSE

XLDnaute Occasionnel

• 7 Décembre 2023

• #5

Bonjour Mapomme,

Super ta solution. 👍

 

Vote positif 0 Vote négatif

Cousinhub

XLDnaute Barbatruc

• 7 Décembre 2023

• #6

Bonsoir,

Eric KERGRESSE à dit:

Il va falloir vous mettre à power Query... 😊

Cliquez pour agrandir...

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

 

Pièces jointes

• PQ_Ncolonnes en 1.xlsx

  18.3 KB · Affichages: 5

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 7 Décembre 2023

• #7

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.

VB:

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

 

Pièces jointes

• Caninge- Liste Val- v1.xlsm

  18.3 KB · Affichages: 8

Dernière édition: 7 Décembre 2023

Vote positif 0 Vote négatif

Caninge

XLDnaute Accro

• 8 Décembre 2023

• #8

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

 

Vote positif 0 Vote négatif

riton00

XLDnaute Impliqué

• 8 Décembre 2023

• #9

Bonsoir,
Autre avec une formule matricielle
Slts

 

Pièces jointes

• Validation de Données sur 4 Colonnes.xlsx

  13.1 KB · Affichages: 5

Vote positif 0 Vote négatif

Caninge

XLDnaute Accro

• 10 Décembre 2023

• #10

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

 

Vote positif 0 Vote négatif

Dan

XLDnaute Barbatruc

• 11 Décembre 2023

• #11

Bonjour,

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

 

Vote positif 0 Vote négatif

Caninge

XLDnaute Accro

• 11 Décembre 2023

• #12

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.

 

Pièces jointes

• Dépenses alimentaires 2021 - 2022 - 2023 - 2024 Essai.xlsm

  541.1 KB · Affichages: 8

Vote positif 0 Vote négatif

Dan

XLDnaute Barbatruc

• 11 Décembre 2023

• #13

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

 

Vote positif 0 Vote négatif

Caninge

XLDnaute Accro

• 11 Décembre 2023

• #14

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.

 

Vote positif 0 Vote négatif

oguruma

XLDnaute Occasionnel

• 11 Décembre 2023

• #15

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 🙂

PowerQuery:

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 🙂

 

Pièces jointes

• Dépenses_alimentaires_Oguruma.xlsm

  561.8 KB · Affichages: 4

Dernière édition: 11 Décembre 2023

Vote positif 0 Vote négatif