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

Angelzeus · 22 Avril 2021

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/21	Divers
CB E.LECLERC 29/03/21	Alimentation
CB LIDL 3393 29/03/21	Alimentation
CB SC.ALIM GENERAL 29/03/21	Alimentation
CB APPLE.COM/BILL 26/03/21	Jeux

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.

job75 · 22 Avril 2021

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 · 22 Avril 2021

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

sylvanu · 22 Avril 2021

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

Angelzeus · 22 Avril 2021

JHA à dit:
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.

Angelzeus · 22 Avril 2021

sylvanu à dit:

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.

sylvanu · 22 Avril 2021

Angelzeus à dit:
Je vais essayer ce soir

Tant qu'à faire essayez avec cette version, issu de votre fichier.

job75 · 22 Avril 2021

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+

Angelzeus · 22 Avril 2021

sylvanu à dit:
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.

sylvanu · 22 Avril 2021

Oups, un p'ti bug d'indice.
En PJ c'est rectifié.

Angelzeus · 23 Avril 2021

sylvanu à dit:
Oups, un p'ti bug d'indice.
En PJ c'est rectifié.

C'est parfait maintenant. J'ai plus qu'a finaliser la liste (580 lignes ) et ça sera niquel. Merci encore !!!

Angelzeus · 23 Avril 2021

Angelzeus à dit:
C'est parfait maintenant. J'ai plus qu'a finaliser la liste (580 lignes ) et ça sera niquel. Merci encore !!!

sylvanu à dit:
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.

sylvanu · 23 Avril 2021

Bonsoir,
Remplacez la ligne 10 par :

VB:

If LCase(tablo(i, 1)) Like LCase(Liste(j, 1)) Then

Fonction LCase (Visual Basic pour Applications)

Référence de langage VBA pour Office

docs.microsoft.com

Angelzeus · 23 Avril 2021

sylvanu à dit:
Bonsoir,
Remplacez la ligne 10 par :

VB:

If LCase(tablo(i, 1)) Like LCase(Liste(j, 1)) Then

Fonction LCase (Visual Basic pour Applications)

Référence de langage VBA pour Office

docs.microsoft.com

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 · 23 Avril 2021

Angelzeus à dit:
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 · 23 Avril 2021

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.

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

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Nouveau

XLDnaute Barbatruc

Discussions similaires