Dérouler 4 recherches dans 4 index différents avec conditions différentes

Bonjour à tous !

J'avais développé il y a quelques temps un fichier excel pour mon équipe, afin d'assigner les emails à une personne définie de mon équipe en fonction de certains mots-clés. Je cherche désormais à ajouter plusieurs conditions / combinaisons mais je pédale un peu dans la semoule pour trouver une solution facile et rapide.

Dans mon fichier initial, j'avais utilisé une formule très bête et très longue pour me renvoyer la valeur que je cherchais... et avec la nouvelle version que je souhaite, ça ne va pas le faire !

Ici, je voulais que 2 conditions soients vraies, puis qu'on me renvoie la valeur de la cellule correspondante dans l'index (voir onglet "OldAssign" du fichier joint) :

=IF(ISNUMBER(SEARCH(TEAM!$C$3;E1;1))*AND(B1=TEAM!$B$3);TEAM!$D$3;
IF(ISNUMBER(SEARCH(TEAM!$C$4;E1;1))*AND(B1=TEAM!$B$4);TEAM!$D$4;
IF(ISNUMBER(SEARCH(TEAM!$C$5;E1;1))*AND(B1=TEAM!$B$5);TEAM!$D$5;
IF(ISNUMBER(SEARCH(TEAM!$C$6;E1;1))*AND(B1=TEAM!$B$6);TEAM!$D$6;
IF(ISNUMBER(SEARCH(TEAM!$C$7;E1;1))*AND(B1=TEAM!$B$7);TEAM!$D$7;
IF(ISNUMBER(SEARCH(TEAM!$C$8;E1;1))*AND(B1=TEAM!$B$8);TEAM!$D$8;
IF(ISNUMBER(SEARCH(TEAM!$C$9;E1;1))*AND(B1=TEAM!$B$9);TEAM!$D$9;
IF(ISNUMBER(SEARCH(TEAM!$C$10;E1;1))*AND(B1=TEAM!$B$10);TEAM!$D$10;
....
""))))))...

Dans ma version future idéale, je souhaiterais un code VBA / formule smart, qui puisse rechercher dans 4 index différents en se basant sur des conditions différentes : d'abord avec les combinaisons du premier index, si pas de match, alors chercher avec les conditions 2 dans l'index 2, etc ; et si aucun match, alors laisser la cellule vide.

Ci-dessous un fichier de Sample - l'onglet TEAM contient les 4 index ; DATA sera la feuille de travail ; RESULT présente le résultat escompté suite aux recherches effectuées.

9teammapping.xlsb (16.48 Ko)

Dans l'idée, je souhaiterai que la Macro déroule quelque chose comme suit :

· Première recherche :

- si DATA-B2 contient TEAM-B5 AND DATA-C2 contient TEAM-C5 alors renvoyer la valeur de TEAM-D5 dans la cellule DATA-E2

(si DATA "Email Object" contient TEAM "Keyword" & DATA "External Address" contient TEAM "External Address" alors renvoyer la valeur de TEAM "Assign to" dans la cellule DATA "Assigned to")

> doit chercher dans toute la table TEAM KWEXT ("Keyword + External Address") - soit de B5 à Bx / de C5 à Cx ; une fois arrivé à la fin de la table, si pas de match, alors passer à la 2e recherche dans le 2e index...

· Deuxième recherche :

- si DATA-C2 contient TEAM-F5 alors renvoyer la valeur de TEAM-G5 dans la cellule DATA-E2

(si DATA "External Address" contient TEAM "External Address" alors renvoyer la valeur de TEAM "Assign to" dans la cellule DATA "Assigned to")

> doit chercher dans toute la table TEAM EXT ("External Address") - soit de F5 à Fx ; une fois arrivé à la fin de la table, si pas de match, alors passer à la 3e recherche dans le 3e index...

· Troisième recherche :

- si DATA-B2 contient TEAM-I5 AND DATA-D2 contient TEAM-J5 alors renvoyer la valeur de TEAM-K5 dans la cellule DATA-E2

(si DATA "Email Object" contient TEAM "Keyword" & DATA "Processing Team" contient TEAM "Processing Team" alors renvoyer la valeur de TEAM "Assign to" dans la cellule DATA "Assigned to")

> doit chercher dans toute la table TEAM KWPRO ("Keyword + Processing Team") - soit de I5 à Ix / de J5 à Jx ; une fois arrivé à la fin de la table, si pas de match, alors passer à la 4e recherche dans le 4e index...

· Quatrième recherche :

- si DATA-B2 contient TEAM-M5 alors renvoyer la valeur de TEAM-N5 dans la cellule DATA-E2

(si DATA "Email Object" contient TEAM "Keyword" alors renvoyer la valeur de TEAM "Assign to" dans la cellule DATA "Assigned to")

> doit chercher dans toute la table TEAM KW ("Keyword") - soit de M5 à Mx ; une fois arrivé à la fin de la table, si pas de match, alors laisser la cellule DATA E2 vide.

La cellule initiale de renvoi sur la feuille DATA est E2 - je souhaite ensuite dérouler cette formule aux lignes suivantes (recherche sur B3 / C3 / D3 et renvoi en E3 ; etc).

En vous remerciant d'avance pour vos conseils & solutions ! D'autant que c'est un sujet que j'ai rencontré plusieurs fois mais je n'ai pas encore la connaissance pour coder ces requêtes basées sur des index, j'ai donc hâte d'apprendre

bonjour,

une proposition

Option Explicit
Sub aargh()
    Dim plage As Range, c As Range
    Dim dict As Object
    Dim wst As Worksheet, wsd As Worksheet
    Dim listcol$, resp$, motsaexclure$, col$, cle$    'string
    Dim liste1, liste2    'variant
    Dim i&, dlwsd&    ' long

    Set wst = Sheets("team")
    Set dict = CreateObject("scripting.dictionary")
    listcol = "BFIM"
    ' creation dictionnaire des responsabilités en fonction des mots clés
    For i = 1 To 4
        col = Mid(listcol, i, 1)
        Set plage = wst.Cells(5, col).Resize(wst.Cells(Rows.Count, col).End(xlUp).Row - 4, 1)
        For Each c In plage
            Select Case i
            Case 1, 3
                cle = UCase(i & c.Value & c.Offset(, 1).Value)
                dict(cle) = c.Offset(, 2)
            Case 2, 4
                cle = UCase(i & c.Value)
                dict(cle) = c.Offset(, 1)
            End Select
        Next c
    Next i

    'sélection du responsable en fonction des paramètres
    motsaexclure = " de "    'liste de mots séparés par des blancs
    Set wsd = Sheets("data")
    dlwsd = wsd.Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To dlwsd
        'recherche 1er critère
        liste1 = creeliste(wsd.Cells(i, 2), " ", motsaexclure)
        liste2 = creelistemail(wsd.Cells(i, 3))
        resp = cherche2("1", dict, liste1, liste2)
        If resp = "" Then    'non trouvé
            'recherche 2ème critère
            liste1 = creeliste(wsd.Cells(i, 3), "#", "#")
            resp = cherche1("2", dict, liste1)
            If resp = "" Then    'non trouvé
                'recherche 3ème critère
                liste1 = creeliste(wsd.Cells(i, 2), " ", motsaexclure)
                liste2 = creeliste(wsd.Cells(i, 4), "#", "#")
                resp = cherche2("3", dict, liste1, liste2)
                If resp = "" Then    'non trouvé
                    'recherche 4ème critère
                    liste1 = creeliste(wsd.Cells(i, 2), " ", motsaexclure)
                    resp = cherche1("4", dict, liste1)
                End If
            End If
        End If
        If resp <> "" Then wsd.Cells(i, 5) = resp
    Next i
End Sub

Function creeliste(kw, sep, mex)
'cree une liste de mots à partir de kw en utilisant sep comme séparateur et en excluant les mots contenu dans mex
    Dim r(), kws, i&, k&
    kws = Split(kw & sep, sep)
    For i = LBound(kws) To UBound(kws) - 1
        If InStr(mex, " " & kws(i) & " ") = 0 Then k = k + 1: ReDim Preserve r(1 To k): r(k) = kws(i)
    Next i
    creeliste = r
End Function

Function creelistemail(kw)
'cree une liste de mail en 1 adresse complète et en 2 adresse partielle "@" nom de domaine
    Dim r(1 To 2)
    r(1) = kw
    r(2) = Mid(kw, InStr(kw, "@"))
    creelistemail = r
End Function

Function cherche2(ind, dict As Object, liste1, liste2)
'recherche la combinaison de mots de liste 1 et liste 2 associée à la priorité de recherche dans le dictionnaire et retourne le responsable
    Dim i&, j&, cle$
    For i = LBound(liste1) To UBound(liste1)
        For j = LBound(liste2) To UBound(liste2)
            cle = UCase(ind & liste1(i) & liste2(j))
            If dict.exists(cle) Then cherche2 = dict(cle): Exit Function
        Next j
    Next i
    cherche2 = ""
End Function

Function cherche1(ind, dict, liste1)
'recherche un mot de liste1 associé à la priorité de recherche dans le dictionnaire et retourne le responsable
    Dim i&, cle$
    For i = LBound(liste1) To UBound(liste1)
        cle = UCase(ind & liste1(i))
        If dict.exists(cle) Then cherche1 = dict(cle): Exit Function
    Next i
    cherche1 = ""
End Function
11teammapping.xlsm (33.02 Ko)

Bonjour Malotrue, h2so4,

Voyez si le fichier joint peut vous aider

Bonjour à vous deux,

Merci beaucoup pour vos retours !

@h2so4 - AMAZING ! Mais pourrais-tu me détailler un peu plus la création de ce code ? Si les tables évoluent est-ce que cela fonctionnera toujours ? J'aime la facilité, mais j'aime également comprendre ce que je fais afin que je puisse faire des corrections / modifications à l'avenir si besoin !

ps. je suis fan du nom du Sub

@njhub - ta solution est très maligne, cependant je ne peux pas passer par Concaténation car le titre du mail ne sera jamais identique - il contiendra le mot-clé mais aura des mots avant et après...

Pour le moment j'essaye de passer par des formules, mais pour une raison obscure que je n'arrive pas à identifier, elles ne fonctionnent pas toutes...

Par exemple celles-ci fonctionnent bien :

=IF(ISNUMBER(SEARCH(TEXT(KWEXT[@Keyword];0);[@[Description]]));TEXT(KWEXT[@[Assign to]];0);"")

=IF(ISNUMBER(SEARCH(TEXT(KWEXT[@[External Address]];0);[@[External Address]]));TEXT(KWEXT[@[Assign to]];0);"")

... et fonctionnent également lorsque mises ensemble avec AND( - pour l'index Keyword + External Address

En revanche la suivante, qui devrait être basique et est identique à la première, ne fonctionne pas du tout :

=IF(ISNUMBER(SEARCH(TEXT(KW[@Keyword];0);[@[Description]]));TEXT(KW[@[Assign to]];0);"")

Vraiment, c'est un mystère...

Re-bonjour,

@h2so4 - en adaptant ta macro à mon fichier Excel, j'ai une erreur Run-time 5 "Invalid Prodecure call or argument" qui sort pour la ligne suivante de Function creelistemail (kw) :

    r(2) = Mid(kw, InStr(kw, "@"))

EDIT: vu d'où venait le problème, dans DATA, j'ai égalements des lignes pour lesquelles il n'y a pas d'adresse mail (nos tickets en interne) - est-il possible d'ajouter une ligne de code pour que la function ignore les cellules vides pour créer la liste ? Merciiii

En essayant sans lignes vides - la function d'attribution pour Keyword + External Address ne fonctionne pô... J'ai bcp de lignes matchant les critères mais le résultat est une cellule vide au lieu du nom de la personne.

Bonjour,

voici une correction pour traiter le cas où il n'y pas d'adresse mail valable.

Normalement le code devrait s'adapter automatiquement à changement du nombre de lignes dans les différentes tables (Eviter de mettre des lignes blanches, ne pas déplacer les tables, ne pas changer le nom des feuilles)

si le nom de la sub ne te plait pas tu peux le changer

14teammapping.xlsm (33.84 Ko)

Mille mercis h2so4 pour la réactivité et la soluce ! Je teste ça demain et je te dis ce qu'il en est.

Et ce n'était pas de l'ironie, me plaît vraiment ce nom de Sub hehe, je garde Je garde également la logique du code, très pratique et qui me servira de nouveau dans le futur !

En passant et juste pour mon info perso, des pistes pour m'éclairer sur le pourquoi du comment les formules IF(ISNUMBER(SEARCH sont si peu fiables / fonctionnent de façon aléatoire ?

C'est étrange pour moi qu'il n'y ai pas de formule simple pour des recherches sur index, type vlookup avec wildcards sur une table entière ! Je ne connais que la combin' exprimée plus haut, ou bien des formules interminables de IF imbriqués faisant référence à ces cellules fixes de l'index... Ou bien la méthode de njhub qui est intéressante également pour des valeurs fixes ! Je vais faire un mot à Microsoft tiens

Rechercher des sujets similaires à "derouler recherches index differents conditions differentes"