Fonction RechV multicritères

Excel
1

Bonjour à tous,

Je sais que c’est un sujet vu et revu mais je n’ai pas réussi à trouver quelque chose qui puisse convenir à mon besoin et les fonctions que j’ai pu trouver en ligne ne donnent rien.

Je suis assez peu calée sous Excel et jusqu’ici mon mari m’aidait beaucoup mais j’ai atteint les limites de ses compétences !

Je cherche une fonction qui puisse, un peu sur le modèle d’un RchV, me permettre de récupérer une donnée sur la base d’au moins deux critères (ou plus…) :

Dans mon fichier, ci-dessous pour l’exemple, j’ai deux onglets :
Dans le premier, des demandes de règlement via un n° de dossier rattachés à un montant : Je peux avoir 2, 3, 4 dossiers identiques pour autant de montants différents, les n° de factures ne permettent pas de discrimination pertinente :
i.e :
ABC20131234 - 150 €
ABC20131234 - 250 €
ABC20131234 - 4890 €

Dans le second onglet, une macro me permet de récupérer dans un système le statut de ces demandes : Réglée / Non réglée ainsi que d’autres informations (N° de virement, date, etc).

Aujourd’hui, j’effectue un RchV sur le n° de dossier qui me renvoie du coup systématiquement la première ligne de mon deuxième onglet sans considération du montant.

ABC20131234 - 150 € - Statut réglé
ABC20131234 - 150 € - Statut réglé
ABC20131234 - 150 € - Statut réglé

Je dois donc derrière passer tous les doublons manuellement pour remonter chacune des informations correcte.

J’aimerais donc une fonction qui me permette globalement de faire, sur l’exemple de mon fichier :

= Rchmulticritère(A2; ‹ Résultats statuts ›!A:A; C2; ‹ Résultats statuts ›!C:C; 3).

3 ici étant du coup l’information dans ma colonne Statut de mon onglet « Résultat statut » mais j’aimerais pouvoir remonter l’information que je souhaite, colonne C, D ou E voire autre selon mon besoin et ma colonne de destination.

J’espère avoir été claire et pouvoir trouver l’aide que je recherche si précieusement sur la question !

Merci d’avance,

Excellente journée,

Ci-dessous, une formule qui répondait justement à mon besoin, mais je ne suis jamais parvenue à la faire fonctionner sur mon fichier… Il me remontait toujours une valeur vide.

Function RECHERCHEVENS(ColonneValeur As Range, critere1 As Variant, PlageRecherche1 As Variant, critere2 As Variant, PlageRecherche2 As Variant, _
Optional Critere3 As Variant, Optional PlageRecherche3 As Variant, _
Optional Critere4 As Variant, Optional PlageRecherche4 As Variant, _
Optional Critere5 As Variant, Optional PlageRecherche5 As Variant)

Auteur : www.Indexmatch.fr
Date : 08/2017
fonction de rechercheV avec plusieurs critères : minimum 2 critères / maximum 5 critères

Dim R1 As Variant 'variable pour le numéro de colonne du critère 1
Dim R2 As Variant 'variable pour le numéro de colonne du critère 2
Dim RF As Variant 'variable pour le numéro de colonne du critère 3
Dim R3 As Variant 'variable pour le numéro de colonne du critère 3
Dim R4 As Variant 'variable pour le numéro de colonne du critère 4
Dim R5 As Variant 'variable pour le numéro de colonne du critère 5

Dim RS1 As String 'variable pour feuille du critère 1
Dim RS2 As String 'variable pour feuille du critère 2
Dim RSF As String 'variable pour feuille du critère 3
Dim RS3 As String 'variable pour feuille du critère 3
Dim RS4 As String 'variable pour feuille du critère 4
Dim RS5 As String 'variable pour feuille du critère 5

Dim counter As Integer 'variable de compteur
Dim lastLine As Long 'variable pour définir la dernière ligne à traiter

R1 = PlageRecherche1.Column
R2 = PlageRecherche2.Column
RF = ColonneValeur.Column

RS1 = PlageRecherche1.Worksheet.Name
RS2 = PlageRecherche2.Worksheet.Name
RSF = ColonneValeur.Worksheet.Name

counter = 0
lastLine = Sheets(RS1).Cells(1, R1).End(xlDown).Row

’ Avec 5 critères -----------

If IsMissing(Critere5) = False And IsMissing(PlageRecherche5) = False Then
    
    R3 = PlageRecherche3.Column
    R4 = PlageRecherche4.Column
    R5 = PlageRecherche5.Column
    
    RS3 = PlageRecherche3.Worksheet.Name
    RS4 = PlageRecherche4.Worksheet.Name
    RS5 = PlageRecherche5.Worksheet.Name

    If critere1 = "" Or critere2 = "" Or Critere3 = "" Or Critere4 = "" Or Critere5 = "" Then
    
        RECHERCHEVENS = "#VALEUR"
        
    Else

        Do While counter = lastLine
        
            counter = counter + 1
            
            If Sheets(RS1).Cells(counter, R1).Value = critere1 And Sheets(RS2).Cells(counter, R2).Value = critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 And _
            Sheets(RS4).Cells(counter, R4).Value = Critere4 And Sheets(RS5).Cells(counter, R5).Value = Critere5 Then
                        
                If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then
                            
                    RECHERCHEVENS = "#N/A"
                            
                Else
                        
                    RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value
                        
                End If
                    
            End If
                
        Loop

    End If

End If

'4 criteres -----------

If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = False And IsMissing(PlageRecherche4) = False Then
    
    R3 = PlageRecherche3.Column 'optional
    R4 = PlageRecherche4.Column 'optional
    
    RS3 = PlageRecherche3.Worksheet.Name 'optional
    RS4 = PlageRecherche4.Worksheet.Name 'optional
    
    If critere1 = "" Or critere2 = "" Or Critere3 = "" Or Critere4 = "" Then
    
        RECHERCHEVENS = "#VALEUR"
        
    Else

        Do While counter = lastLine
        
            counter = counter + 1
            
            If Sheets(RS1).Cells(counter, R1).Value = critere1 And Sheets(RS2).Cells(counter, R2).Value = critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 And _
            Sheets(RS4).Cells(counter, R4).Value = Critere4 Then
                        
                If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then
                            
                    RECHERCHEVENS = "#N/A"
                            
                Else
                        
                    RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value
                        
                End If
                    
            End If
                
        Loop
        
    End If

End If

' Avec 3 critères -----------

If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = True And IsMissing(PlageRecherche4) = True And _
IsMissing(Critere3) = False And IsMissing(PlageRecherche3) = False Then
    
    R3 = PlageRecherche3.Column
    
    RS3 = PlageRecherche3.Worksheet.Name

    If critere1 = "" Or critere2 = "" Or Critere3 = "" Then
    
        RECHERCHEVENS = "#VALEUR"
        
    Else

        Do While counter = lastLine
        
            counter = counter + 1
            
            If Sheets(RS1).Cells(counter, R1).Value = critere1 And Sheets(RS2).Cells(counter, R2).Value = critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 Then
                        
                If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then
                            
                    RECHERCHEVENS = "#N/A"
                            
                Else
                        
                    RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value
                        
                End If
                    
            End If
                
        Loop

    End If

End If

' Avec 2 critères -----------

If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = True And IsMissing(PlageRecherche4) = True And _
IsMissing(Critere3) = True And IsMissing(PlageRecherche3) = True Then

    If critere1 = "" Or critere2 = "" Then

        RECHERCHEVENS = "#VALEUR"
    
    End If
    
    Do While counter = lastLine
    
        counter = counter + 1
        
        If Sheets(RS1).Cells(counter, R1).Value = critere1 And Sheets(RS2).Cells(counter, R2).Value = critere2 Then
                    
            If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then
                        
                RECHERCHEVENS = "#N/A"
                        
            Else
                    
                RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value
                    
            End If
                
        End If
            
    Loop

End If

End Function

Fichier Exemple RchVMC.xlsx (9,9 Ko)

2

Bonjour,
Si j’ai compris
Fichier Exemple RchVMC.xlsx (16,0 Ko)

3

Bonjour mimimathy !

Merci de votre retour aussi rapide !

Alors du coup, j’aimerais comprendre la logique derrière si possible.

Je sollicitais une formule VBA parce qu’elle aurait été modulable plus simplement mais si ça peut fonctionner, ça me va ! Ce sera juste plus long à retenir.

=INDEX(colonne où se trouve ce que je veux remonter); EQUIV(Critère1&Critère2; colonne1&colonne2; à quoi correspond le 0 ?)

Je peux du coup le faire sur autant de critères que je veux en ajoutant des &critèreX et des &colonneX ?

J’avais vu des combinaisons de ces deux fonctions mais complètement incompréhensibles et là ça me semble tellement « simple » !

Je vais tester sur mon fichier du coup original de ce pas !

4

Re,
Ah si c’est en VBA, non problème, je regarde
Par contre, pour la formule, c’est une matricielle

FORMULE MATRICIELLE
Appuyer sur les touches : Ctrl+Maj+entrée pour valider.
Des crochets apparaissent de part et d’autre de la fonction dans le champs de la cellule : {}

Et si il y a des dizaines de lignes, une matricielle va ralentir les calculs

5

Re,
Avec macro
Fichier Exemple RchVMC.xlsm (23,6 Ko)

6

Re,

Merci pour ce retour aussi rapide ! Je suis désolée, je suis totalement débutante et autodidacte et jusqu’il y a quelques mois, tout ça c’était totalement du chinois pour moi.

Je peux du coup moduler comme je veux selon les colonnes ? En effet, l’exemple que j’ai donné est hyper simplifié avec 5 colonnes mais dans mon cas de figure, j’en ai une trentaine sur les deux onglets avec des onglets potentiellement différents.

Idem, le fichier source initial est différent tous les mois bien que très fortement similaire sur la forme.

Est-ce que mon interprétation ci-dessous est correcte en reprenant le texte de la macro ?

Option Explicit

Sub Test()
'********************************
'* DECLARATIONS DES VARIABLES *
'********************************
Dim i%, j%, Dl%, lr%
Dim Ws As Worksheet, Wd As Worksheet
'********************************
'* INITIALISATION DES VARIABLES *
'********************************
Set Ws = Sheets(« Résultats statuts ») > ici j’indique je présume la feuille dans laquelle je vais récolter les datas ?
Set Wd = Sheets(« Base ») > ici j’indique la feuille vers laquelle je transfère mes datas
Dl = Ws.Range(« A » & Rows.Count).End(xlUp).Row > je ne touche à rien ici
lr = Wd.Range(« A » & Rows.Count).End(xlUp).Row > je ne touche à rien ici

For i = 2 To Dl **> je ne touche à rien ici**
  For j = 2 To lr **> je ne touche à rien ici**
    If Ws.Cells(j, 1).Value = Wd.Cells(i, 1).Value And _

>si je comprends bien, ici on demande de vérifier que ma donnée X dans ma colonne 1 est identique à ma donnée Y dans ma colonne 2 : Peu importe la place de ma donnée ?)

      Ws.Cells(j, 2).Value = Wd.Cells(i, 4).Value Then

> Idem ici pour ma colonne 2 et ma colonne 4? j’aurais donc juste à moduler mes noms de colonne et si je veux des critères supplémentaires, ajouter des And_ ?)

        Wd.Cells(i, 5).Value = Ws.Cells(j, 3).Value 
        Wd.Cells(i, 6).Value = Ws.Cells(j, 4).Value
        Wd.Cells(i, 7).Value = Ws.Cells(j, 5).Value

** > ici je lui demande juste quelle donnée reporter c’est bien cela ? Du coup, si je veux qu’il reporte d’autres données, j’aurais juste à rajouter les colonnes cibles et les colonnes sources ?

    End If
  Next j
Next i

End Sub

Désolée de le décortiquer autant mais j’aime bien comprendre pour pouvoir gagner en autonomie derrière.

Encore merci pour votre aide !

7

Re,
Tout à fait
A savoir que les cellules de destination sont dans la feuille « Wd » et la source en « Ws »
il suffit juste de faire correspondre les N° de colonne entre les deux onglets
Je vais te remettre la macro commentée

8

Merci infiniment ! Je vais essayer de ce pas sur mon fichier de base.

J’ai un moyen de faire en sorte d’enregistrer la macro pour que quelque soit le fichier Excel, je puisse l’appeler et qu’elle ne soit pas rattachée simplement à un classeur ?

Encore désolée pour toutes les questions peut-être un peu idiotes…

9

Re,
voilà la macro commentée

Sub Test()
  '********************************
  '*  DECLARATIONS DES VARIABLES  *
  '********************************
  Dim i%, j%, Dl%, lr%
  Dim Ws As Worksheet, Wd As Worksheet
  '********************************
  '* INITIALISATION DES VARIABLES *
  '********************************
  Set Ws = Sheets("Résultats statuts")
  Set Wd = Sheets("Base")
  Dl = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Dernière ligne remplie de l'onglet source
  lr = Wd.Range("A" & Rows.Count).End(xlUp).Row 'Dernière ligne remplie de l'onglet destination

    For i = 2 To Dl 'Je boucle de la ligne 2 à la derniere pour la destination
      For j = 2 To lr 'Je boucle de la ligne 2 à la derniere pour la source
        If Ws.Cells(j, 1).Value = Wd.Cells(i, 1).Value And _
          Ws.Cells(j, 2).Value = Wd.Cells(i, 4).Value Then
          'Si la cellule de l'onglet source (A2) est = la cellule de l'onglet destination (A2) et
          'la cellule de l'onglet source (B2) est = la cellule de l'onglet destination (D2), alors
            
            Wd.Cells(i, 5).Value = Ws.Cells(j, 3).Value 'la cellule destination (E2) = la cellule source (C2), etc
            Wd.Cells(i, 6).Value = Ws.Cells(j, 4).Value
            Wd.Cells(i, 7).Value = Ws.Cells(j, 5).Value
        End If
      Next j 'Je boucle sur les cellules de la source jusqu'à la dernière ligne remplie
    Next i 'Si j'ai fini la boucle de la source, je prend la valeur suivante dans destination, et je fais la boucle complète sur la source, etc
End Sub
1 « J'aime »
10

Super merci !

Du coup, je viens de la tester sur l’un de mes fichiers.

J’ai tout bien mis à jour mais il fallait bien un mais… quand je la lance, elle s’arrête sur cette ligne et j’ai une erreur : Dépassement de capacité :

lr = Wd.Range(« A » & Rows.Count).End(xlUp).Row

J’ai recherché et vu que ça pouvait être dû au format des variables.

Du coup en mettant « As String » pour définir les variables i, j, dl, lr, la macro semble tourner après avoir testé avec des points d’arrêt mais elle met tellement de temps à analyser le fichier qu’elle finit par faire planter Excel…

Une idée ?

Je vais peut-être rester sur la formule du coup. Elle était longue niveau traitement mais ça a pu fonctionner jusqu’au bout !

11

Re,
Non, String c’est pour du texte
J’avais mis « % » pour Integer (c’est l’abréviation)
mais si tu as plus de 25000 lignes, il faut les déclarer en As Long (& pour le raccourci)

12

Le As String c’est ce qu’utilisait mon mari pour les macros qu’il m’a codé puisque je peux avoir des données textes dans le lot.

Mais je vais tester avec le As long puisqu’effectivement je suis largement à plus de 25.000 lignes ! Merci :slight_smile:

Mais du coup, j’ai compris le pourquoi de la lenteur.

Comme pour chaque ligne, elle va vérifier, tous les milliers de lignes de la feuilles « résultats statut », le temps de traitement en est d’autant plus rallongé.

J’ai testé du coup en bloquant le for i = 2 to Dl pour le faire passer uniquement sur les 100 premières lignes.

Sur le principe ça fonctionne du coup plutôt bien mais avec une macro qui tourne très très très longtemps.

13

Re,

Cela n’a rien à voir,
DL ou LR ou I ou J ce sont des N° de ligne que tu boucles pour trouver des données, rien à voir avec du texte
Ensuite la macro est faite pour l’exemple que tu donnes, si tu avais précisé qu’il y avait 25000 lignes ou plus, c’est à faire d’une autre façon

14

Bonjour Mimimathy
Une petite suggestion ici :

If Ws.Cells(j, 1).Value = Wd.Cells(i, 1).Value And _
Ws.Cells(j, 2).Value = Wd.Cells(i, 4).Value Then
'Si la cellule de l’onglet source (A2) est = la cellule de l’onglet destination (A2) et
'la cellule de l’onglet source (B2) est = la cellule de l’onglet destination (D2), alors

        Wd.Cells(i, 5).Value = Ws.Cells(j, 3).Value 'la cellule destination (E2) = la cellule source (C2), etc
        Wd.Cells(i, 6).Value = Ws.Cells(j, 4).Value
        Wd.Cells(i, 7).Value = Ws.Cells(j, 5).Value
    End If

Le traitement dans cette boucle ne peux y passer qu une fois
Pourquoi donc la continuer autant l arrêter une fois qu il y est passé en rajoutant avant le End if l instruction Exit for
Ainsi à supposer que dès la première ligne cette partie soit sollicitée les conditions sont trouvées les données récupérées on arrête la boucle qui sinon ira jusqu’à la dernière ligne numéro 25000 une perte de temps inutile
Qu en penses tu ?
C’est à regarder ne crois tu pas ?
Mais peut être je me trompe à toi de me dire
Sinon je pense un allègement non négligeable du temps de traitement

15

Bonjour FFO,
Tu as raison, mais au départ l’exemple ne contient que 3 lignes (je supposais que cela serait évolutif mais pas avec plus 25000 lignes
D’autres part, impossible de suivre l’avancement, le premier post du demandeur étant modifié au fur et à mesure. donc aucun suivi.
Il est sur qu’un Exit For après chaque comparaison trouvé augmenterait la vitesse, tout comme un ScreenUpdating et un calcul Manuel
Cependant, avec les demandes qui au départ sont pour 2 critères, puis 3 pour arriver à 5, ne sachant plus ou et quand s’arrêtera les recherches, je reste muet, et j’attend qu’un nouveau post soit placé en fin de fil et refaire une macro avec soit un dictionnaire ou un tableau :wink:

16

Merci pour ton avis
Il est vrai que pour proposer une solution optimum il faut avoir de manière précise tout le context
Je te laisse oeuvrer dans cet esprit tu auras la main appropriée à cette fin
Je ne me fais pas de souci
Bonne suite

17

Bonjour à tous les deux,

Merci de vos réponses.

Pour le critère, dans mon post initial je précise bien au moins deux critères mais je souhaitais pouvoir effectivement y recourir y compris si j’avais deux critères ou plus.

Le code que j’ai mis était celui que j’ai essayé d’utiliser sans succès, si ça pouvait aider, et qui va de 2 à 5 critères en effet. Mais ça me donnait systématiquement un résultat vide.

My apologies, je n’ai effectivement fait qu’un exemple très limité, inventé pour des raisons de confidentialité mais je n’ai précisé que plus tard que mon fichier de travail faisait plusieurs dizaines de milliers de lignes.

C’est d’ailleurs la raison principale de ma volonté d’automatiser ce contrôle que je fais aujourd’hui manuellement en checkant mes doublons à la main pour vérifier que l’info correcte est remontée.

18

Re,
En prenant un peu de temps pour monter un classeur avec des données non confidentielles et des demandes précises, tout en respectant la continuité du post, je pourrais toujours jeter un oeil.
Mais s’il faut reprendre depuis le premier post et tout relire car les posts sont modifiés, c’est sans moi

19

Bonjour

Je te propose de combiner une procédure VBA avec des formules
Dans une colonne libre j’ai pris la colonne M pour les 2 onglets « Base » et « Résultats statuts » j’ai par formule concaténé les données conditions (N° Dossier et Montant)
Cette formule colonne M est à mettre en place dans les 2 onglets
Puis je ne me sers que d’une seule boucle qui balaie toutes les lignes de l’onglet « Résultats statuts »
A chaque ligne de cet onglet je cherche la donnée condition colonne M dans la même colonne de l’autre onglet
Si celle-ci est trouvé je ramène les données souhaitées

L’avantage est de n’utiliser qu’une boucle
De plus les données à concaténer données conditions sont à ta main donc peuvent être au nombre à ta convenance l’essentiel étant qu’elles soient identiques dans la colonne M des 2 onglets (même conditions et surtout dans le même ordre)

Inconvénient il faut gérer ces formules dans leur composition et dans l’ensemble des lignes
On utilise un mixte VBA formule peut être moins orthodoxe

J’ai intégré dans le fichier ci-joint 10000 lignes à traiter avec les même données à rapportées pour plus de facilité

Onglet « Base »
Cellule N1 la ligne traitée
Cellule O1 l’heure de départ du traitement
Cellule O2 l’heure de fin du traitement

Le temps de traitement est de 1 minute pour 10000 lignes

Pour changer la formule de colonne il faudra adapter le code

Une solution qui offre l’avantage d’être rapide

Testes et dis moi

Fichier Exemple RchVMC2.xlsm (820,2 Ko)

20

Re,
Après pour aller un peu plus vite, en triant les colonnes
Fichier Exemple RchVMC2.xlsm (555,1 Ko)

1 « J'aime »