Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

[jp77100]

Bonjour a tous
Je suis un vrais débutant pour les macros mais avec 4500 lignes a traiter je ne pense pas avoir un autre choix.
Voila j'explique :
j'ai un classeur avec 2 feuilles dans la feuille 1 (produits) j'ai 3 colonnes qui determine le produit la B la D et la E dans ma feuille 2(categories) je trouve les memes valeurs dans les colonnes B D et F avec en plus un numero dans celle ci en colonne G.
J'aimerais que quand l'on trouve dans la feuille produits les memes designations que la feuille categories, cela copie dans la feuille produits la valeur de la colonne G de la feuille 2 dans la colonne H de la feuille 1.
Humm pas tres clair peut etre, je vous mets mon fichier exemple.
Merci d'avance car apres plusieurs jour j'ai trouve une macro que j'ai modifiée pour une autre fonction, mais cette fois ci je ne trouve rien

 

[Dranreb]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Bonjour
Un essai par formule
À+

 

[jp77100]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Bonjour
Un essai par formule
À+

Bonjour Dranreb

Merci de ton aide, mais le resulat est mauvais car je devrais avoir 92 pour les 4 premieres ligne, 88 pour ligne 12 a 14 , 87 de la ligne 17 a 20 etc .
a bientot

 

[Dranreb]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Ah oui, je avais oublié le 3ième critère
Il suffit de rajouter en colonne P une formule très proche de celle de la O et de baser l'INDEX sur cette colonne.
À+

 

[jp77100]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

re bonjour

Oui mais impossible d'ajouter des lignes a chaque fois mon tableau complets fait plus de 4500 lignes et sur les colonnes j'arrive a AD.

Alors imagine les sources d'erreur, c'est pour cela que je pensais a une macro un peu comme celle qui est deja dans me modele mais en allant cherché la valeur dans l'autre feuille.

Merci encore

 

[Dranreb]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

J'ai écrit cette fonction personnalisée à mettre dans un module ordinaire:

Function IndexRub(PlgDon As Range, PlgRub As Variant, Arg As Variant) As Variant
Dim LDéb As Long, NbrL As Long, LReste As Long
On Error Resume Next
LDéb = WorksheetFunction.Match(Arg, PlgRub, 0): If Err Then IndexRub = "": Exit Function
LReste = PlgRub.Rows.Count - LDéb
NbrL = WorksheetFunction.Match("*", PlgRub.Rows(LDéb + 1).Resize(LReste), 0): If Err Then NbrL = LReste + 1
Set IndexRub = Intersect(PlgDon.EntireColumn, PlgRub.Rows(LDéb).Resize(NbrL).EntireRow)
End Function

De sorte que cette formule en H2 recopiée vers le bas semble donner le résultat souhaité:

=IndexRub(Categories!$G$1;IndexRub(Categories!$F$1;IndexRub(Categories!$D$1;Categories!$B$3:$B$114;$B2);$D2);$E2)

À+

 

[Fo_rum]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Bonsoir,

vois si cela fait l'affaire : en complétant la feuille2 (colonne B)

 

[jp77100]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

J'ai écrit cette fonction personnalisée à mettre dans un module ordinaire:

Function IndexRub(PlgDon As Range, PlgRub As Variant, Arg As Variant) As Variant
Dim LDéb As Long, NbrL As Long, LReste As Long
On Error Resume Next
LDéb = WorksheetFunction.Match(Arg, PlgRub, 0): If Err Then IndexRub = "": Exit Function
LReste = PlgRub.Rows.Count - LDéb
NbrL = WorksheetFunction.Match("*", PlgRub.Rows(LDéb + 1).Resize(LReste), 0): If Err Then NbrL = LReste + 1
Set IndexRub = Intersect(PlgDon.EntireColumn, PlgRub.Rows(LDéb).Resize(NbrL).EntireRow)
End Function

De sorte que cette formule en H2 recopiée vers le bas semble donner le résultat souhaité:

=IndexRub(Categories!$G$1;IndexRub(Categories!$F$1;IndexRub(Categories!$D$1;Categories!$B$3:$B$114;$B2);$D2);$E2)

À+

bonjour Dranreb
Ta solution marche pas mal mais des cases reste vide et pour le moment je ne trouve pas pourquoi c'est incomprehensible
je cherche

 

[jp77100]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Bonsoir,

vois si cela fait l'affaire : en complétant la feuille2 (colonne B)

Bonjour Fo_rum
Pas mal ta solution elle me plait bien, par contre si par compléter la feuille 2 c'est a dire la feuille categorie en complétant es nom dans la colonne B ben cela ne prend pas tout et je n'arrive pas a comprendre pourquoi meme si j'ai meme pas reussi a modifier de facon a ce que l'ecriture de la categorie soit en I au lieu de H car dans H j'ai deja une macro qui vient ecrire ici.

Merci de ton aide

 

[jp77100]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

re bonjour Fo_rum
Je viens de trouver la raison si jamais un mot est ecrit Jet au lieu de jet dans l'autre tableau cela ne fonctionne pas, meme chose si un espace existe apres le mot.
J'ai également réussi a inscrire dans la colonne I au lieu de H

Private Sub CommandButton1_Click()
    Dim DLiP As Long, DLiC As Long, Li As Long, Lig As Long
    Dim TabloP, TabloC
    DLiP = Cells(Rows.Count, 1).End(xlUp).Row
    TabloP = Range("B2:I" & DLiP)
    With Sheets("Categories")
        DLiC = .Cells(Rows.Count, 1).End(xlUp).Row
        TabloC = .Range("B2:I" & DLiC)
    End With
    For Li = 2 To DLiP
        For Lig = 2 To DLiC
            If TabloP(Li - 1, 1) & " " & TabloP(Li - 1, 3) & " " & TabloP(Li - 1, 4) = _
               TabloC(Lig - 1, 1) & " " & TabloC(Lig - 1, 3) & " " & TabloC(Lig - 1, 5) Then
                TabloP(Li - 1, 8) = TabloC(Lig - 1, 6)
            End If
        Next
    Next
    Range("B2:I" & DLiP) = TabloP
End Sub

Une solution pour eviter cela ?
Merci d'avance

 

[Dranreb]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Bonjour

Ta solution marche pas mal mais des cases reste vide et pour le moment je ne trouve pas pourquoi

BROTHER, Matricielle: Cat4 non spécifiée
"Jet d'encre" à mettre au lieu de "Jet d''encre"
Mais le plus vicieux: CANON, Traceur, Jet d'encre: "Traceur " avec un blanc en trop dans Catégories

La fonction peut être corrigée comme suit pour repérer plus facilement les erreurs:

Function IndexRub(PlgDon As Range, PlgRub As Variant, Arg As Variant) As Variant
Dim LDéb As Long, NbrL As Long, LReste As Long
If TypeName(PlgRub) = "String" Then IndexRub = PlgRub
On Error Resume Next
LDéb = WorksheetFunction.Match(Arg, PlgRub, 0): If Err Then IndexRub = """" & Arg & """?": Exit Function
LReste = PlgRub.Rows.Count - LDéb
NbrL = WorksheetFunction.Match("*", PlgRub.Rows(LDéb + 1).Resize(LReste), 0): If Err Then NbrL = LReste + 1
Set IndexRub = Intersect(PlgDon.EntireColumn, PlgRub.Rows(LDéb).Resize(NbrL).EntireRow)
End Function

Par ailleurs j'ai réfléchi: pour être sûr que la fonction soit réévaluée quand il faut, il vaut mieux spécifier des colonnes entières pour le 1er paramètre:

=IndexRub(Categories!$G:$G;IndexRub(Categories!$F:$F;IndexRub(Categories!$D:$D;Categories!$B$3:$B$114;$B2);$D2);$E2)

À+

 

[jp77100]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Bonjour Dranreb

Ta formule fonctionne vraiment pas mal, merci beaucoup
Par contre sais tu si il y a un moyen afin de celle ci ne fasse pas la différence en tre majuscule et minuscule
J'explique en exemple : Jet D'encre ou Jet d'encre que cela fonctionne ?
Sinon pas grave on trouveras une autre solution comme remplacer d'excel par exemple
Merci d'avance et pour ton aide
Cordialement

 

[Dranreb]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Bonjour.

sais tu si il y a un moyen afin de celle ci ne fasse pas la différence en tre majuscule et minuscule

C'est le cas: extrait de l'aide :

La fonction EQUIV ne distingue pas les majuscules des minuscules lorsqu'elle donne l'équivalence de valeurs de texte.

Et Match non plus puisque c'est la même.
Par contre j'ai vu un oubli: pour bien renvoyer la 1ère rubrique non trouvée, nouvelle ligne 3:

If TypeName(PlgRub) = "String" Then IndexRub = PlgRub: Exit Function

À+

 

[jp77100]

Re : Excel comparer reference en tre feuille 1 et 2 et prendre valeur de la colonne G

Bonjour.C'est le cas: extrait de l'aide :Et Match non plus puisque c'est la même.
Par contre j'ai vu un oubli: pour bien renvoyer la 1ère rubrique non trouvée, nouvelle ligne 3:

If TypeName(PlgRub) = "String" Then IndexRub = PlgRub: Exit Function

À+

Merci Dranreb
Cela fonctionne très bien, je n'aurais plus qu'a corriger les cellules manquantes et les erreurs d'ecritures
Bonne journée