XL 2019 Auto compléter une cellule en fonction d'une liste

Angelzeus

XLDnaute Nouveau
Bonjour,

Tout d'abord je voulais vous dire un grand merci pour votre site, il est vraiment top.
J'aurais besoin d'aide pour une formule impliquant le NB.SI
Ma formule fonctionne mais est limité à 64 imbrications et j'aimerais pourvoir en faire bien plus.

Je m'explique, j'ai un tableau qui récapitule mon relevé de compte dans lequel il y a 2 colonnes (A "Lib" et B "Catégorie"). Je voudrais que la colonne B se remplisse automatiquement en fonction d'une liste qui se trouve sur une autre feuille nommée "List".

Exemple de la liste:

Colonne A Colonne B
*8 A HUIT*Alimentation
*ALIM GENERAL*Alimentation
*ALIMENT*Alimentation
*AMAZON*Divers
*ATELIER DU SOUR*Divers
*BC 2 PANTIN*Alimentation
*BIG FERNAND*Alimentation
*BURGER KING*Alimentation
*CAMAIEU*Habits

IMPORTANT: J'ai mis les textes entre astérisques afin qu'il recherche cette chaine de caractère.

Dans le tableau principale, la colonne Lib ressemble à ça:




Colonne A "Lib"Colonne B "Catégorie"
CB AMAZON PAYMENTS 31/03/21Divers
CB E.LECLERC 29/03/21Alimentation
CB LIDL 3393 29/03/21Alimentation
CB SC.ALIM GENERAL 29/03/21Alimentation
CB APPLE.COM/BILL 26/03/21Jeux

Par exemple sur la première ligne, il doit identifier "Amazon" puisque cette chaine de caractère se trouve bien dans la liste et doit donc automatiquement compléter la colonne Catégorie avec "Divers".

Voici la formule que j'utilise et que j'écris dans les cellules de la colonne B "Catégorie" du tableau principale :

# L: La feuille Liste
# @lib: La colonne Lib du tableau principale

=(SI(NB.SI([@Lib];'L'!$A$1);'L'!$B$1;SI(NB.SI([@Lib];'L'!$A$2);'L'!$B$2;SI(NB.SI([@Lib];'L'!$A$3);'L'!$B$3;SI(NB.SI([@Lib];'L'!$A$4);'L'!$B$4;SI(NB.SI([@Lib];'L'!$A$5);'L'!$B$5;SI(NB.SI([@Lib];'L'!$A$6);'L'!$B$6;SI(NB.SI([@Lib];'L'!$A$7);'L'!$B$7;SI(NB.SI([@Lib];'L'!$A$8);'L'!$B$8;SI(NB.SI([@Lib];'L'!$A$9);'L'!$B$9;SI(NB.SI([@Lib];'L'!$A$10);'L'!$B$10;SI(NB.SI([@Lib];'L'!$A$11);'L'!$B$11;SI(NB.SI([@Lib];'L'!$A$12);'L'!$B$12;SI(NB.SI([@Lib];'L'!$A$13);'L'!$B$13;SI(NB.SI([@Lib];'L'!$A$57);'L'!$B$57;SI(NB.SI([@Lib];'L'!#REF!);'L'!#REF!;SI(NB.SI([@Lib];'L'!$A$14);'L'!$B$14;SI(NB.SI([@Lib];'L'!$A$15);'L'!$B$15;SI(NB.SI([@Lib];'L'!$A$16);'L'!$B$16;SI(NB.SI([@Lib];'L'!$A$17);'L'!$B$17;SI(NB.SI([@Lib];'L'!$A$18);'L'!$B$18;SI(NB.SI([@Lib];'L'!$A$19);'L'!$B$19;SI(NB.SI([@Lib];'L'!$A$20);'L'!$B$20;SI(NB.SI([@Lib];'L'!$A$21);'L'!$B$21;SI(NB.SI([@Lib];'L'!$A$22);'L'!$B$22;SI(NB.SI([@Lib];'L'!$A$23);'L'!$B$23;SI(NB.SI([@Lib];'L'!$A$24);'L'!$B$24;SI(NB.SI([@Lib];'L'!$A$25);'L'!$B$25;SI(NB.SI([@Lib];'L'!$A$26);'L'!$B$26;SI(NB.SI([@Lib];'L'!$A$27);'L'!$B$27;SI(NB.SI([@Lib];'L'!$A$28);'L'!$B$28;SI(NB.SI([@Lib];'L'!$A$29);'L'!$B$29;SI(NB.SI([@Lib];'L'!$A$30);'L'!$B$30;SI(NB.SI([@Lib];'L'!$A$31);'L'!$B$31;SI(NB.SI([@Lib];'L'!$A$32);'L'!$B$32;SI(NB.SI([@Lib];'L'!$A$33);'L'!$B$33;SI(NB.SI([@Lib];'L'!$A$34);'L'!$B$34;SI(NB.SI([@Lib];'L'!$A$35);'L'!$B$35;SI(NB.SI([@Lib];'L'!$A$36);'L'!$B$36;SI(NB.SI([@Lib];'L'!$A$37);'L'!$B$37;SI(NB.SI([@Lib];'L'!$A$38);'L'!$B$38;SI(NB.SI([@Lib];'L'!$A$39);'L'!$B$39;SI(NB.SI([@Lib];'L'!$A$40);'L'!$B$40;SI(NB.SI([@Lib];'L'!$A$41);'L'!$B$41;SI(NB.SI([@Lib];'L'!$A$42);'L'!$B$42;SI(NB.SI([@Lib];'L'!$A$43);'L'!$B$43;SI(NB.SI([@Lib];'L'!$A$44);'L'!$B$44;SI(NB.SI([@Lib];'L'!$A$45);'L'!$B$45;SI(NB.SI([@Lib];'L'!$A$46);'L'!$B$46;SI(NB.SI([@Lib];'L'!$A$47);'L'!$B$47;SI(NB.SI([@Lib];'L'!$A$48);'L'!$B$48;SI(NB.SI([@Lib];'L'!$A$49);'L'!$B$49;SI(NB.SI([@Lib];'L'!$A$50);'L'!$B$50;SI(NB.SI([@Lib];'L'!$A$51);'L'!$B$51;SI(NB.SI([@Lib];'L'!$A$52);'L'!$B$52;SI(NB.SI([@Lib];'L'!$A$53);'L'!$B$53;SI(NB.SI([@Lib];'L'!$B$54);'L'!$B$54;SI(NB.SI([@Lib];'L'!$B$55);'L'!$B$55;SI(NB.SI([@Lib];'L'!$A$56);'L'!$B$56;SI(NB.SI([@Lib];'L'!$A$57);'L'!$B$57;SI(NB.SI([@Lib];'L'!$A$58);'L'!$B$58;SI(NB.SI([@Lib];'L'!$A$59);'L'!$B$59;SI(NB.SI([@Lib];'L'!$A$60);'L'!$B$60;SI(NB.SI([@Lib];'L'!$A$61);'L'!$B$61;SI(NB.SI([@Lib];'L'!$A$62);'L'!$B$62;"")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Le problème est que je suis limité à 64 imbrications avec la formule SI alors qu'il m'en faudrait 200 au moins.

Si vous pouvez m'aider, ça serait super sympa. J'ai essayé avec la formule RECHERCHEV, XLOOKUP ou même DECALER mais sans succès. Je ne suis pas un expert du tout.

Merci à vous.
 
Dernière édition:
Solution
Bonjour Angelzeus, JHA, sylvanu,

J'ai supprimé mon message initial.

Sur Excel 2019 la fonction JOINDRE.TEXTE existe.

Donc voyez le fichier joint et cette formule matricielle en colonne B de la 2ème feuille :
Code:
=JOINDRE.TEXTE(" ; ";VRAI;SI(NB.SI([@Libellé];Liste!$A$1:$A$100);Liste!D$1:D$100;""))
à valider par Ctrl+Maj+Entrée.

CB est en doublon parce qu'il y a une rubrique CB* en colonne A de la 1ère feuille.

A+

JHA

XLDnaute Barbatruc
Bonjour à tous,

Peux-tu nous joindre un fichier de quelques lignes si la réponse ne te convient pas.
VB:
=INDEX(Feuil2!$B$2:$B$13;EQUIV(1;EQUIV("*"&Feuil2!$A$2:$A$13&"*";Feuil1!A2;0);0))

JHA
 

Pièces jointes

  • Essai catégorie.xlsx
    11.2 KB · Affichages: 15

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Angelzeus, Job, JHA,

S'il y a des centaines de lignes avec des centaines de catégories alors une formule matricielle risque d'être lourde, autant passer en VBA :
VB:
Sub Trouve()
    Application.ScreenUpdating = False
    Dim Liste, tablo, i%, j%
    Liste = Sheets("Liste").Range("A2:B" & Sheets("Liste").Range("A65500").End(xlUp).Row)
    With ActiveSheet
        DL = .Range("A65500").End(xlUp).Row
        tablo = .Range("A2:B" & DL)
        For i = 1 To UBound(tablo)
            For j = 1 To UBound(Liste)
                If tablo(i, 1) Like Liste(j, 1) Then
                    tablo(i, 2) = Liste(j, 2)
                    Exit For
                End If
            Next j
        Next i
        .Range("$A$2").Resize(UBound(tablo, 1), UBound(tablo, 2)) = tablo
    End With
End Sub
 

Pièces jointes

  • Classeur2.xlsm
    16.8 KB · Affichages: 8

Angelzeus

XLDnaute Nouveau
Bonjour à tous,

Peux-tu nous joindre un fichier de quelques lignes si la réponse ne te convient pas.
VB:
=INDEX(Feuil2!$B$2:$B$13;EQUIV(1;EQUIV("*"&Feuil2!$A$2:$A$13&"*";Feuil1!A2;0);0))

JHA
Voici le fichier demandé. La feuille Liste est complète. Pour le tableau principale, j'ai enlevé 99% des lignes. Il n'y a rien de confidentiel dedans. Pour info il est en xlsm parce qu'il y a une macro dedans qui me permet de mettre en couleur les cellules qui ont une formule.
 

Pièces jointes

  • Exemple.xlsm
    23 KB · Affichages: 9

Angelzeus

XLDnaute Nouveau
Bonjour Angelzeus, Job, JHA,

S'il y a des centaines de lignes avec des centaines de catégories alors une formule matricielle risque d'être lourde, autant passer en VBA :
VB:
Sub Trouve()
    Application.ScreenUpdating = False
    Dim Liste, tablo, i%, j%
    Liste = Sheets("Liste").Range("A2:B" & Sheets("Liste").Range("A65500").End(xlUp).Row)
    With ActiveSheet
        DL = .Range("A65500").End(xlUp).Row
        tablo = .Range("A2:B" & DL)
        For i = 1 To UBound(tablo)
            For j = 1 To UBound(Liste)
                If tablo(i, 1) Like Liste(j, 1) Then
                    tablo(i, 2) = Liste(j, 2)
                    Exit For
                End If
            Next j
        Next i
        .Range("$A$2").Resize(UBound(tablo, 1), UBound(tablo, 2)) = tablo
    End With
End Sub

Je suis d'accord avec toi que le VBA pourrait surement être une bien meilleure solution. Je vais essayer ce soir pour voir ce que ça donne. Merci pour ta réponse.
 

job75

XLDnaute Barbatruc
Bonjour Angelzeus, JHA, sylvanu,

J'ai supprimé mon message initial.

Sur Excel 2019 la fonction JOINDRE.TEXTE existe.

Donc voyez le fichier joint et cette formule matricielle en colonne B de la 2ème feuille :
Code:
=JOINDRE.TEXTE(" ; ";VRAI;SI(NB.SI([@Libellé];Liste!$A$1:$A$100);Liste!D$1:D$100;""))
à valider par Ctrl+Maj+Entrée.

CB est en doublon parce qu'il y a une rubrique CB* en colonne A de la 1ère feuille.

A+
 

Pièces jointes

  • Exemple(1).xlsm
    18.7 KB · Affichages: 11

Angelzeus

XLDnaute Nouveau
Tant qu'à faire essayez avec cette version, issu de votre fichier.
Je viens de tester, et ça fonctionne très bien. Un grand merci à toi !!!! J'adore cette version là.

Une petite amélioration à faire serait que lorsqu'un libellé ne correspond à rien dans la liste, il faudrait qu'il n'affiche rien.
Pour le moment, j'obtiens un message d'erreur du VB disant "Erreur d’exécution 9, l'indice n'appartient pas à la sélection."

Comme ça, ça me permettra de voir lesquelles des libellés ne sont pas dans la liste. Je te rejoins le fichier en PJ puisque j'ai juste modifier l'ordre des colonnes. Comme ça tu pourras voir par toi-même en ligne 24 le problème.

Merci,
Matthieu.
 

Pièces jointes

  • Exemple V3.xlsm
    24.9 KB · Affichages: 4

Angelzeus

XLDnaute Nouveau
C'est parfait maintenant. J'ai plus qu'a finaliser la liste (580 lignes ;)) et ça sera niquel. Merci encore !!!
Oups, un p'ti bug d'indice.
En PJ c'est rectifié.
Après finalisation de mon tableau, j'ai une petite demande en plus:

1- C'est possible aussi de faire en sort que la recherche ne soit pas case sensitive ? Parce que là apparemment ça l'est.
Par exemple j'ai plusieurs ligne avec Deezer pour lequel c'était écrit DEEZER et d'autre Deezer. AU lieu d'avoir deux lignes dans la liste écrit en minuscule et majuscule, ça serait mieux d'en avoir qu'un seul.


Petite info: Si dans le tableau principale un filtre est en cours, il ne faut surtout pas lancer la macro. Il faut d'abord enlever le filtre. Pour éviter ce problème, il y a surement un moyen de dire d'enlever le filtre avant de remplir le tableau via le VB?

Merci à toi.
 
Dernière édition:

Angelzeus

XLDnaute Nouveau
Bonsoir,
Remplacez la ligne 10 par :
VB:
If LCase(tablo(i, 1)) Like LCase(Liste(j, 1)) Then

Merci, c'est bon ça fonctionne bien. Je suppose que vous n'avez pas vu le dernier message puisque j'ai édité le post très peu de temps avant de voir votre réponse.

"Petite info: Si dans le tableau principale un filtre est en cours, il ne faut surtout pas lancer la macro. Il faut d'abord enlever le filtre. Pour éviter ce problème, il y a surement un moyen de dire d'enlever le filtre avant de remplir le tableau via le VB?"
 

Angelzeus

XLDnaute Nouveau
Merci, c'est bon ça fonctionne bien. Je suppose que vous n'avez pas vu le dernier message puisque j'ai édité le post très peu de temps avant de voir votre réponse.

"Petite info: Si dans le tableau principale un filtre est en cours, il ne faut surtout pas lancer la macro. Il faut d'abord enlever le filtre. Pour éviter ce problème, il y a surement un moyen de dire d'enlever le filtre avant de remplir le tableau via le VB?"

Je viens de tester avec l'enregistreur de macro. Je suppose qu'il y a un moyen facile de dire d'enlever tous les filtres sur tout le tableau. Parce que ça c'est pas super joli et surtout si je rajoute une colonne plus tard, ça ne la prendre pas en compte. (J'ai 8 colonnes pour le moment).

VB:
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8
 

job75

XLDnaute Barbatruc
Bonsoir Angelzeus, le fil,

Il serait quand même sympathique de votre part de nous dire ce que donne la solution par formule de mon post #7 avec votre liste de 580 lignes.

D'autant plus qu'avec cette solution les filtres ne posent pas de problème.

Bonne nuit.
 

Discussions similaires

Réponses
8
Affichages
364

Statistiques des forums

Discussions
311 725
Messages
2 081 947
Membres
101 849
dernier inscrit
florentMIG