Filtrer une feuille de donnée par des références situées dans une autre feuille.

[Franckxxx]

Bonjours à tous!

J'effectue actuellement mon premier stage en entreprise et je suis confronté à un problème plutôt compliqué (pour mon niveau en tout cas..).
Je souhaite créer un filtre automatique pour extraire des données d'une base de donnée qui varie au cours du temps.
En prenant pour exemple le fichier ci-joint, je cherche a sélectionner (récupérer) les lignes de la feuille "donnée" dont la référence est présente dans la feuille "référence" et par conséquent éliminer les autres données.

Cependant, les références ne sont pas exactement identiques.. En effet, seul les premiers caractères sont identiques comme on peut le voir sur le fichier exemple (j'ai mit en couleur les références de la base de donnée présentes dans les références recherchée pour les retrouver facilement).

Voila donc mon problème. J'ai déjà avancé dans différentes directions pour le résoudre mais a chaque fois cela c'est soldé par un échec. Je préfère donc poster l'origine du problème et non mon avancée si jamais une solution simple existe.
Mon fichier de base de donnée comportant en moyenne 6000lignes, il n'est pas possible de le faire manuellement. J'essaye donc de rajouter cette opération au macro que je suis entrain de créer.

Toutes les solutions, même partielles, sont les bienvenues.
Si vous voulez des explications supplémentaire, n'hésitez pas à me demander et merci d'avance!

 

[Paf]

Re : Filtrer une feuille de donnée par des références situées dans une autre feuille.

Bonjour

Un code qui copie en feuille Différence (qui doit être crée) les lignes de la feuille Données dont la référence comprend en tout ou partie une référence de la feuille Références.
Utilisation de tableau pour améliorer la vitesse d'exécution.

Sub Franckxxx()
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Dim DerLig1 As Long, DerLig2 As Long, DerLig3 As Long, NumLig3 As Long, i As Long, j As Long, k As Long
Dim TabDonnées, TabRéférence

Set Ws1 = Worksheets("Données")
Set Ws2 = Worksheets("Références")
Set Ws3 = Worksheets("Différence")

Ws3.Cells.Delete 'effacement de la feuille Différence
Ws3.Range("A1:F1") = Ws1.Range("A1:F1").Value 'copie de la ligne de titre

DerLig1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row 'Dernière ligne Feuille Données
DerLig2 = Ws2.Range("A" & Rows.Count).End(xlUp).Row 'Dernière ligne Feuille Références
NumLig3 = 2 'N° de ligne pour l'écriture en feuille Différence
TabDonnées = Ws1.Range("A2:F" & DerLig1) ' mise en tableau des données de la feuille Données
TabRéférence = Ws2.Range("B2:B" & DerLig2) ' mise en tableau de la colonne référence de la feuille Références

For i = LBound(TabDonnées) To UBound(TabDonnées) ' de la première à la dernière ligne du tableau TabDonnées
    For j = LBound(TabRéférence) To UBound(TabRéférence) ' de la première à la dernière ligne du tableau TabRéférence
        If TabDonnées(i, 1) Like TabRéférence(j, 1) & "*" Then 'si ref Données comme ref Références
            For k = 1 To 6 'écriture en feuille Différence
                Ws3.Cells(NumLig3, k) = TabDonnées(i, k)
            Next
            NumLig3 = NumLig3 + 1 'incrémentation du N° de ligne
        End If
    Next
Next

End Sub

A+

 

[Chefredo]

Re : Filtrer une feuille de donnée par des références situées dans une autre feuille.

Bonsoir à tous,

s'il s'agit seulement de filtrer sur la feuille données selon la colonne B de la feuille références, un filtre élaboré doit faire l'affaire. En effet, sans que je sache bien pourquoi, le fait que seuls les 1ers caractères soient identiques n'a pas l'air de poser problème. En tout cas, ça fonctionne pour ton exemple.

Pour simplifier, j'ai créé des listes nommées dynamiques (qu'on peut remplacer par "tableau" (XL2007 et suivants) ou "liste" (XL2003).
liste nommée "base" : feuilles "données" y compris les en-tête
liste nommée "réf" : colonne B de la feuille "références" y compris en-tête
On filtre "base" en se servant de "réf" comme zone de critères et zou !
Si on y tient, petite macro liée à un bouton pour lancer le filtre :

Sub filtre()
Range("base").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("réf"), Unique:=False
End Sub

et une autre liée à un autre bouton pour afficher la liste non filtrée :

Sub affich()
ActiveSheet.ShowAllData
End Sub

Chefredo

 

[Franckxxx]

Re : Filtrer une feuille de donnée par des références situées dans une autre feuille.

Bonjour à tous,
Tout d'abord merci de l’intérêt porté à mon problème.

Chefredo, j'ai essayé d'appliquer votre solution à mon fichier principal et cela n'a pas marché et j'avoue ne pas très bien comprendre comment modifier les lignes de codes pour les adapter à mon fichier

Paf, mon fichier étant légèrement différent de l'exemple que j'avais posé j'ai adapté la première partie pour que cela fonctionne (notamment les délimitations de colonnes) mais cela ne fonctionne pas. La feuille Différence affiche la ligne de départ mais ensuite lorsque je lance la macro, excel cherche longtemps mais ne trouve aucune référence similaire. Je pense avoir bien compris le code mais une partie reste abstraite pour moi et je pense donc que mon erreur vient d'ici:
If TabDonnées(i, 1) Like TabRéférence(j, 1) & "*" Then 'si ref Données comme ref Références
For k = 1 To 6 'écriture en feuille Différence
Ws3.Cells(NumLig3, k) = TabDonnées(i, k)
Next
NumLig3 = NumLig3 + 1 'incrémentation du N° de ligne
Je ne comprend pas la signification de "*" ainsi que la fonction de k. Je suppose qu'il s'agit d'une fonction permettant de résoudre le problème des références identiques qui ont pourtant leurs caractères de fins différents mais je n'en suis pas sur.
Merci d'avance si vous pouvez m'éclairer sur ces points obscurs.

Cordialement.

 

[Paf]

Re : Filtrer une feuille de donnée par des références situées dans une autre feuille.

Re,

Re,
les données (colonnes A à F) de la feuille Données sont mises dans le tableau TabDonnées
les références (colonnes B) de la feuille Référencessont mises dans le tableau TabRéférence
pour chaque référence contenu dans le tableau TabDonnées(i, 1) on balaye toutes les rérérences du tableau TabRéférence(j, 1)

* est utilisé comme Joker remplacant plusieurs caractères
dans If TabDonnées(i, 1) Like TabRéférence(j, 1) & "*", on compare TabDonnées(i, 1) avec TabRéférence(j, 1) par exemple:

si TabDonnées(i, 1) =TG6352B-00 et TabRéférence(j, 1)=EB139* le test est faux , on passe au suivant
si TabDonnées(i, 1) =TG6352B-00 et TabRéférence(j, 1)=TG6352B* le test est vrai, on fait le traitement prévu

For k = 1 To 6 'écriture en feuille Différence
Ws3.Cells(NumLig3, k) = TabDonnées(i, k)
Next

il y a 6 colonnes dans la feuille Données et dans le tableau TabDonnées
sur la première ligne utile de la feuille Différence on copie :
quand k=1 en colonne 1 le contenu de la "colonne" 1 du tableau TabDonnées
quand k=2 en colonne 2 le contenu de la "colonne" 2 du tableau TabDonnées
...
...

j'espère mes explications suffisantes.

A+

 

[Franckxxx]

Re : Filtrer une feuille de donnée par des références situées dans une autre feuille.

Re,
D'accord je comprend mieux l'utilité du k maintenant. J'ai appliqué les modifications nécessaires et en effet le filtre a fonctionné.
Merci a vous!

 

[Franckxxx]

Re : Filtrer une feuille de donnée par des références situées dans une autre feuille.

Re-Bonjour,

J'aimerais ajouter une spécificité au macro précédent.
La problématique liée à mon projet nécessite la présence de données annexes sur la feuille finale pour que je puisse exploiter les résultats.

Concrètement, en reprenant l'exemple 2 si dessus, j'aimerais que les données des colonnes "donnée1" et "donnée2" de la feuille Références apparaissent dans la feuille Différence (même si cette dernière doit toujours comporter l'ensemble des données filtrées de la feuille Données), de telle sorte que ses données apparaissent sur la ligne de leur référence associée.

Cependant, je n'ai aucune idée comment m'y prendre.
Merci à vous si vous pouvez m'éclairer sur ce point. Je reste disponible pour toute explication si je n'ai pas été assez clair sur un point.
Merci d'avance pour votre intérêt.

Cordialement.

 

[Paf]

Re : Filtrer une feuille de donnée par des références situées dans une autre feuille.

re,

on peut modifier le code de cette façon:

If TabDonnées(i, 1) Like TabRéférence(j, 1) & "*" Then 'si ref Données comme ref Références
            For k = 1 To 6 'écriture en feuille Différence
                Ws3.Cells(NumLig3, k) = TabDonnées(i, k)
            Next

            ' ** rajout des données 1 et données 2
            Ws3.Cells(NumLig3, k) = Ws2.Cells(j + 1, 4)
            Ws3.Cells(NumLig3, k + 1) = Ws2.Cells(j + 1, 5)
            ' ** fin rajout

            NumLig3 = NumLig3 + 1 'incrémentation du N° de ligne
        End If

On veut écrire des données dans les colonnes 7 et 8 de la feuille Différence.
en sortant de la boucle For k..., k vaut 7 d'où Ws3.Cells(NumLig3, k)= et Ws3.Cells(NumLig3, k + 1)=
Dans le tableau TabDonnées il y a les références de la feuille Références dans le même ordre. La première référence a l'indice 1 dans le tableau mais se trouve en ligne 2 dans la feuille. La deuxième référence a l'indice 2 dans le tableau mais se trouve en ligne 3 dans la feuille. etc... donc un décalage de 1.
Quand le test If TabDonnées(i, 1) Like TabRéférence(j, 1)... est vrai, la référence a pour indice j dans le tableau, le N° de ligne de cette référence dans la feuille sera donc j+1

les données que l'on veut copier de la feuille Références sont en colonnes 4 et 5, connaissant le N° de ligne on aura :Ws2.Cells(j + 1, 4) et Ws2.Cells(j + 1, 5)

espérant avoir été clair

A+

 

[Franckxxx]

Re : Filtrer une feuille de donnée par des références situées dans une autre feuille.

D'accord, j'avais cherché à intégrer la commande à la boucle voila surement pourquoi cela ne marchais pas.
Merci encore pour votre aide, le code est maintenant plus clair pour moi.