Microsoft 365 text.contains dans une chaîne de caractére

[GADENSEB]

Bonjour Le forum
Je travaille sur la formule "text.contains" de power query
Le résultat des requêtes se trouve dans l'onglet "conversion"
Le but est de comparer la base "Import" dans la base "Commerces" et de rapatrier le "Groupement"


J'avais fait un premier travail avec des formules mais cela avait ses limites

XL 2016 - retrouver une partie si une partie du texte d'une cellule est compris dans une BDD

Bonjour le forum Je cherche une formule pour retrouver une partie si une partie du texte d'une cellule est compris dans une BDD et renvoyer cette valeur Je suppose que c'est avec TROUVE ? Bonne journée à vous

www.excel-downloads.com

Donc je teste une version sous PowerQuery.

La formule text.contains fonctionne bien quand je cherche et trouve une une donnée "facile" , ici en exemple "BABA BOO BIBI"
Par contre, si "BABA BOO BIBI"se trouve en dans une chaine de caractére "1121ezfzefze BABA BOO BIBI ezfzefez" la formule text.contains ne marche pas......

Comment contourner le problème ?


Bonne journée
Sébastien

 

[chris]

Bonjour

Dans la mesure ou BABA BOO BIBI contient BABA cela pose un problème pour distinguer les deux dans une comparaison

 

[GADENSEB]

Je sais bien c'est tt mon problème
et en recherchant la plus grande chaine de caractére "BABA BOO BIBI" si trouve pas cela veut dire que cherche la petite chaîne de caractère "BABA"

 

[Hasco]

Bonjour,
Bonjour @chris,

Si c'est le nom du commerce que vous cherchez dans LIBELLE BANQUE c'est :

Text.Contains([LIBELLE BANQUE],[COMMERCES.COMMERCES]))

Et non

Text.Contains([COMMERCES.COMMERCES],[LIBELLE BANQUE])

Par contre vous aurez une erreur si COMMERCES.COMMERCES est null. Soit vous remplacez les null par "" (ce que je ne recommande pas) soit vous tester l'erreur soit try....otherwise.... :

each try Text.Contains([LIBELLE BANQUE],[COMMERCES.COMMERCES]) otherwise null

 

[job75]

Bonjour GADENSEB, chris, Hasco,

Je vais vous dire pourquoi je n'aime guère Power Query : c'est parce que son codage n'est pas beaucoup plus facile à comprendre que VBA et que son utilisation est moins souple.

Voyez le fichier joint et cette macro dans le code de la feuille CONVERSION :

Private Sub Worksheet_Activate()
Dim tablo, ub&, a, uba&, i&, j%
'---copie le tableau source---
tablo = [Tableau1].Resize(, 5) 'matrice, plus rapide
ub = UBound(tablo)
'---tableau de la feuille COMMERCES---
With [Tableau2]
    .Columns(3) = "=LEN(RC[-2])" 'taille du texte en colonne A
    .ListObject.Range.Sort .Cells(1, 3), xlDescending, Header:=True 'tri décroissant
    .Columns(3).Delete xlToLeft
    a = .Resize(, 2)
    uba = UBound(a)
End With
'---remplissage des 4ème et 5ème colonnes---
For i = 1 To ub
    tablo(i, 4) = "": tablo(i, 5) = "" 'RAZ de sécurité
    For j = 1 To uba
        If InStr(tablo(i, 3), a(j, 1)) Then _
            tablo(i, 4) = a(j, 1): tablo(i, 5) = a(j, 2): Exit For
Next j, i
'---restitution---
With [Tableau3]
    .Resize(ub) = tablo
    .Rows(ub + 1).Resize(Rows.Count - .Rows(ub).Row).Delete xlUp 'RAZ en dessous
End With
End Sub

Elle se déclenche quand on active la feuille.

A+

 

[chris]

RE

c'est bien connu, quand il ne comprend pas

​

Pour un Français : c'est du chinois ou de l'hébreu​

Pour un Allemand ou un Tchèque : c'est de l'espagnol​

Pour un Anglais : c'est du grec ou du hollandais​

Pour un Espagnol ou un grec : c'est du chinois​

Pour un Italien : c'est de l'arabe​

Pour un Hollandais : c'est du chinois ou de l'hébreu ou du latin​

Pour un Portugais : c'est du chinois ou du grec​

...​

​

Il suffit d'apprendre une langue pour changer de point de vue...​

 

[Hasco]

Re,

Bon alors sans doute que le swahili passera encore moins bien 🙂

Avec une fonction personnalisée à l'étape 3 "Commerce cherché" de la requête "Conversion" du fichier joint

Cordialement

 

[job75]

Avec les solutions de chris #6 et Hasco #7 le manque de souplesse de Power Query est clair.

Quand on modifie les données des feuilles IMPORT ou COMMERCES la feuille CONVERSION ne se modifie pas automatiquement, il faut l'actualiser manuellement ou par VBA.

Pour chris : le résultat que tu obtiens n'est pas satisfaisant.

 

[chris]

RE
C'est corrigé

Il est vrai que nous ne faisons du PowerQuery que depuis une demi-douzaine d'années contrairement au VBA manipulé depuis 29 ans donc on n'optimise pas toujours 😡

Sur un autre forum, un intervenant qui a totalement, ou presque, abandonné VBA pour PowerQuery et PowerPivot nous donne des codes M hyper concis et efficaces que cela en est époustouflant.

Mais nous nous améliorons...

 

[GADENSEB]

Hello.
Merci a tous pour la rapidité de vos réponses !
Et la bonne humeur sur ce fil 😉
Je test vos codes ce soir.

Bonne soirée
Seb

 

[GADENSEB]

Hello
Merci pour vos codes,
J'ai pris le temps de regarder chacun.
Merci c'est très instructif pour apprendre PowerQuery.

en réfléchissant je me rend compte que j'ai essayé de faire simple mais au final j'ai fais compliqué.
Le code de Job75 en réponse 5 parait simplifier tout le probléme.

si je résume :
- Je prends les datas en "Tableau1"
- Je compare la colonne "LIBELLE BANQUE" avec la colonne "Commerces" du "tableau2" qui est trié en ordre croisssant
- Je rapartie les datas sélectionnées dans le "tableau3"

Simple et efficace.

du coup, je me suis dit que de passer passer par plusieurs successif, on pourrait :
- Sélectionne le "tableau1"
- Faire le système de comparaison avec le "tableau2"
-Réimplanté le "tableau1" avec ce qui est "merger"en "tableau 2"

de ce résultat obtenu, je génére d'autres tableau, du coup, j'ai revu un peu mon process et réintégrer le tableau final.

j'ai mis en exemple mon fichier
Données de base en vert et données à obtenir en bleu

j'ai essayé de bouger le code mais je bute au moment de :
reporter la colonne en G (merge du tableau2) quand cette colonne n'est pas à coté de la colone de recherche, ici en E


j’espère avoir été clair.

Bonne soirée

Sébastien

 

[job75]

Bonsoir GADENSEB, le fil,

Je repasse par ici et je me rends compte d'une erreur grossière sur mon fichier (1) du post #5.

Les tableaux des 1ère et 3ème feuilles ayant 4 lignes il y a bug si l'on supprime une ligne en 1ère feuille.

Dans ce fichier (2) j'ai corrigé en remplaçant l'instruction :

.Rows(ub + 1).Resize(Rows.Count - .Rows(ub).Row).Delete xlUp 'RAZ en dessous

par :

If .Rows.Count > ub Then .Rows(ub + 1).Resize(.Rows.Count - ub).Delete xlUp 'RAZ en dessous

Bonne nuit.

 

[job75]

Pour ce qui est du dernier problème posé voyez ce fichier (3) et le code la 1ère feuille :

Private Sub Worksheet_Activate()
Worksheet_Change [A1] 'lance la macro
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, uba&, tablo, i&, j%
'---tableau de la feuille COMMERCES---
With [Tableau2]
    .Columns(3) = "=LEN(RC[-2])" 'taille du texte en colonne A
    .ListObject.Range.Sort .Cells(1, 3), xlDescending, Header:=True 'tri décroissant
    .Columns(3).Delete xlToLeft
    a = .Resize(, 2)
    uba = UBound(a)
End With
'---remplissage des 6ème et 7ème colonnes---
tablo = [Tableau1].Resize(, 7) 'matrice, plus rapide
For i = 1 To UBound(tablo)
    tablo(i, 6) = "": tablo(i, 7) = "" 'RAZ de sécurité
    For j = 1 To uba
        If InStr(tablo(i, 5), a(j, 1)) Then _
            tablo(i, 6) = a(j, 1): tablo(i, 7) = a(j, 2): Exit For
Next j, i
'---restitution---
Application.EnableEvents = False 'désactive les évènements
[Tableau1] = tablo
Application.EnableEvents = True 'réactive les évènements
End Sub

Il se déclenche quand on valide ou modifie une cellule quelconque ou qu'on active la feuille.

Re bonne nuit.

 

[GADENSEB]

Hello
Merci pour ce nouveau code.
C'est presque cela.
J'avais dû mal faire ma phrase plus haut en fait je ne voudrais plus que la recherche raméne la colonne G "COMMERCES".
La colonne F "Soiurce.Name" doit être vide.

il y a qqc que je ne comprends pas dans le code
---> pourquoi surprimer les datas en "Tableau2" ? .Columns(3).Delete xlToLeft ???
est-ce obligatoire?
Est-ce que l'on peut faire un tableau "Virtuel" ?
J'ai vraiment besoin que le "Tableau2" reste en l'état. --> pas de suppression de datas.
ce tableau me sert dans la suite du programme.

Merci à toi

Bonne am

Seb

 

[job75]

il y a qqc que je ne comprends pas dans le code
---> pourquoi surprimer les datas en "Tableau2" ? .Columns(3).Delete xlToLeft ???
est-ce obligatoire?

La colonne C de la 2ème feuille est utilisée comme colonne auxiliaire pour le tri du tableaau

Après utilisation je la supprime car elle devient inutile mais si ça vous gêne gardez-la, vous verrez bien...

Le tableau doit pouvoir être modifié manuellement, pourquoi parler de tableau virtuel ?