Données manquantes par rapport 2 dates (Dictionary)

[cathodique]

Bonjour,

Je me tourne vers la communauté, sollicitant votre aide. En effet, je n'ai pas d'idée pour trouver une bonne approche du problème.

Voici le topo de mon travail. Via Userform, je reconstitue sur la feuille "menu", un canevas extrait de la feuille "BD_Ref". L'extraction du canevas est basée sur 3 données [Type, Famille et Sous-Famille]

Je le complète avec une date, un procédé de Test , des mesures et je le transfert par macro dans la feuille "BD_Test".

Les procédés de test sont de 4 (col R, feuille BD_Test), seuls les procédés M/A et M seront pris en compte dans la procédure.

je voudrais qu'un message me signale les données manquantes dans la BD_Test.

En effet, pour chaque combinaison [Famille|Sous-Famille] de BD_Ref doit être présente dans BD_Test avec une date et un procédé (test) .

Je voudrais donc trouver les combinaisons qu'on retrouve pas sur la feuille BD_Test avec une date . Mais il faut aussi tenir compte de l'année d'acquisition des familles.

En effet, il peut pas y avoir dans BD_Test des données antérieures à la date d'acquisition.

Les données de références étant répétées, j'ai commencé par utiliser 2 dictionnaires, mais pour la suite je ne sais pas quelle serait la meilleure approche pour trouver une solution assez rapide, vu que le nombre de lignes est assez conséquent.

Sur la feuille recap, vous trouverez le résultat à obtenir. Il y aussi un tableau des dates d'acquisition.

Au final, c'est d'avoir un message m'indique BD_Test "à jour" ou un listing [date, famille, sous-famille] manquantes,

En espérant avoir été clair, je vous remercie par avance.

ps: la sous-famille: M1 n'est pas prise en compte dans le dictionnaire DicRef (sous-famille cédée mais existe dans BD_Test)

 

[Bebere]

Re : Données manquantes par rapport 2 dates (Dictionary)

bonjour Cathodique
regarde ce code qui donne les familles,sous familles présentent dans test par rapport à ref
familles présentes en p et manquantes en r de test
et dis moi

Sub PV_Manquant()
Dim Ltest As Integer, L As Long, i As Integer, j As Long, c As Long, ii As Long
Dim Tbtest, Tbref, tbManquant(), TbT(), Tbtest2()
Dim DicTest As Object, D As New Dictionary, DicRef As New Dictionary
Dim tblDate(1 To 8, 1 To 2), a, b(), cle, Dt
Dim BdRef As Worksheet, BdTest As Worksheet, item, item1, test As Boolean, x

Set BdRef = ThisWorkbook.Worksheets("BD_Ref")
Set BdTest = ThisWorkbook.Worksheets("BD_Test")
Set DicRef = CreateObject("Scripting.Dictionary")
Set DicTest = CreateObject("Scripting.Dictionary")

'Tableau date d'acquisition
tblDate(1, 1) = "T1": tblDate(1, 2) = 1960
tblDate(2, 1) = "H2": tblDate(2, 2) = 1960
tblDate(3, 1) = "O1": tblDate(3, 2) = 1982
tblDate(4, 1) = "G1": tblDate(4, 2) = 1986
tblDate(5, 1) = "L1": tblDate(5, 2) = 1996
tblDate(6, 1) = "G2": tblDate(6, 2) = 2000
tblDate(7, 1) = "DL1": tblDate(7, 2) = 2004
tblDate(8, 1) = "RO1": tblDate(8, 2) = 2006
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Feuil3.Cells.Clear '***pour tester à retirer du code
'
' Feuil3.[J2].Resize(UBound(tblDate, 1), UBound(tblDate, 2)).Value = tblDate '***pour tester

L = BdRef.Range("A" & Rows.Count).End(xlUp).Row
Tbref = BdRef.Range("c2:d" & L).Value
'famille M1 exclue
For L = 1 To UBound(Tbref)
If Tbref(L, 1) <> "M1" Then D(Tbref(L, 1) & "-" & Tbref(L, 2)) = Tbref(L, 1) & "-" & Tbref(L, 2)
Next L
'i = 0
'For Each Item In Dfamille.Count
'i = i + 1
'ReDim Preserve Tbref(1 To 3, 1 To i): Tbref(1, 1) = Item
'
'Next Item
L = BdTest.Range("A" & Rows.Count).End(xlUp).Row
Tbtest = BdTest.Range("A2:R" & L).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

For i = 1 To UBound(Tbtest) 'M et M/A inclu,M1 exclu
If Tbtest(i, 4) <> "M1" Then
If Tbtest(i, 18) = "M" Or Tbtest(i, 18) = "M/A" Then 'on ne prend que pour Test= M ou M/A
If Tbtest(i, 5) <> "" Then DicTest(Tbtest(i, 4) & "-" & Tbtest(i, 5) & "-" & Tbtest(i, 3)) = Tbtest(i, 4) & "-" & Tbtest(i, 5) & "-" & Tbtest(i, 3)
End If
End If
Next i
i = 0: j = 0
'compare ref et test
For Each item In D.items
test = True
For Each item1 In DicTest.items
If UCase(Left(item1, Len(item))) = UCase(item) Then
i = i + 1
ReDim Preserve TbT(1 To i): TbT(i) = item1
test = False: Exit For
End If
Next item1
If test Then
j = j + 1
ReDim Preserve tbManquant(1 To j): tbManquant(j) = item
'MsgBox item 'manquant dans tbref
End If
Next item

For i = 1 To UBound(TbT)
Feuil6.Range("P" & i + 1) = TbT(i)
Next i

For i = 1 To UBound(tbManquant)
Feuil6.Range("R" & i + 1) = tbManquant(i)
Next i

' For j = 1 To UBound(Tbref)
' If Tbref(j, 1) <> "M1" Then 'on ne prend pas la famille M1
' ' MsgBox Tbref(j, 2)
' DicRef(Tbref(j, 1) & "|" & Tbref(j, 2)) = ""
' End If
' Next j
'
' Feuil3.[P2].Resize(DicRef.Count) = Application.Transpose(DicRef.keys)
' Feuil3.[R2].Resize(D.Count) = Application.Transpose(D.keys)
'
' MsgBox D.Count
' Stop

' For i = 1 To UBound(tblDate)
' 'MsgBox tblDate(i, 1) & "|" & tblDate(i, 2)
' ' c = 0
' For j = 1 To UBound(Tbtest)
' D(Tbtest(j, 3)) = "" 'd(Tbtest(j, 4)) = "" '///colonne date
' If Tbtest(j, 18) = "M" Or Tbtest(j, 18) = "M/A" Then 'on ne prend que pour Test= M ou M/A
' If Tbtest(j, 4) <> "M1" Then '////pour exclure M1 du traitement
' If Year(Tbtest(j, 3)) <= tblDate(i, 2) Then '/////on extrait année de Tbtest pour comparer
' 'cle=famille&sous-famille -----> item= date test
' ' If Not DicTest.Exists(Tbtest(j, 4) & Tbtest(j, 5)) Then DicTest.Add Tbtest(j, 4) & Tbtest(j, 5), Tbtest(j, 3)
' ' d(Tbtest(j, 3)) = ""
' DicTest(Tbtest(j, 4) & "|" & Tbtest(j, 5) & "|" & Tbtest(j, 3)) = ""
' 'If Not d.exists(Tbtest(j, 3)) Then d.Add Tbtest(j, 3), Tbtest(j, 3)
'
' End If
' End If
' End If
' Next j
' Next i
' Feuil3.[H2].Resize(DicTest.Count) = Application.Transpose(DicTest.keys)

End Sub

 

[cathodique]

Re : Données manquantes par rapport 2 dates (Dictionary)

Bonsoir Bebere,

Franchement c'est trop gentil de ta part. J'avoue qu'aujourd'hui je ne me suis pas trop pris la tête. J'ai consulté pas mal de discussions et essayé de donner un coup de main.

Merci beaucoup pour ton code, il plantait au niveau des déclarations sur Dim D As New Dictionary, DicRef As New Dictionary
J'ai rectifié par as Object et j'ai rajouté Set D = CreateObject("Scripting.Dictionary") et ça a fonctionné.
Le code a renvoyé les données présentes en BD_Test pour 1993 et 2006, mais ne renvoie pas celle de 2005.
Pour les données manquantes il ne renvoie qu'une seule, celle de 2006. Il en manque 2 de 2005.

Est-ce que mon dernier énoncé a été plus clair? Autrement dit, les couples (Famille, sousfamille) de la feuille BD_Ref doivent obligatoirement se trouver sur la feuille BD_Test avec une date. Quels sont les couples de BD_Ref qui n'ont pas de date dans BD_Test.

Toute ma gratitude.

 

[cathodique]

Re : Données manquantes par rapport 2 dates (Dictionary)

Re,

Je pense bien que j'ai très exposé mon problème. Voici comment, je vois le process. Si on le faisait sur la feuille la procédure serait très lent. On boucle sur les dates de Bd_test sans doublons, on met la date sur chaque ligne de BD_Ref, mais avant de comparer les 2 feuilles on exclut les familles dont la date d'acquition est supérieure à la date du test. à la suite de ça on compare les 2 BD pour extraire de BD_Ref celle qui n'existent pas dans BD_test. Et on passe à la date suivante et ainsi de suite.

Vu le nombre des données à traiter, les dictionnaires ou les tableaux sont plus rapides. Je les maitrises pas bien et même pour traduire mon raisonnement en code je peine.

Merci à tous ceux qui apporteront leur gracieuse contribution.

 

[Bebere]

Re : Données manquantes par rapport 2 dates (Dictionary)

bonjour Cathodique
pour dictionary il faut cocher microsoft scripting runtime dans menu outils,références
si il manque des dates c'est parce qu'il y a des familles sans sous famille et j'ai éliminé aussi d'autres lignes
je regarde pour la suite

 

[Bebere]

Re : Données manquantes par rapport 2 dates (Dictionary)

Cathodique
voilà un autre code
mon avis,tu as plus d'absent que prévu
j'ai fait quelques comparaisons avec le filtre,çà a l'air cohérent

 

[cathodique]

Re : Données manquantes par rapport 2 dates (Dictionary)

Bonjour Bebere,

Je te remercie beaucoup pour tout ce que tu fais.
En effet, il fallait que je coche microsoft scripting runtime dans menu outils/références.
Mais en déclarant "As Object", le code a fonctionné.

Pour les sous-familles, il n'est pas possible que certaines soient vides. Ceci pour les tests M et M/A.
Pour les 2 autres Tests (CMDP et CSt), c'est vrai dans la majorité des cas il n'y a pas de sous-famille.
Mais ceux-ci sont exclus dans le code lui-même.

If Tbtest(i, 18) = "M" Or Tbtest(i, 18) = "M/A" Then    'on ne prend que pour Test= M ou M/A

Le dernier code renvoie cette combinaison "RO1|MD/S5", qui n'a aucun enregistrement dans BD_Test.

Bonne journée.

Oups! je crois qu'on vient de se croiser. Merci.

 

[cathodique]

Re : Données manquantes par rapport 2 dates (Dictionary)

mon avis,tu as plus d'absent que prévu

Si tu as travaillé avec le dernier fichier joint, il n'y en a que 3 d'absents.

voilà un autre code

Merci, mais tu as omis de joindre le code.

Avec ou sans résultat concluants, 1000 mercis, trop gentil de ta part.

 

[Bebere]

Re : Données manquantes par rapport 2 dates (Dictionary)

le voilà,un mauvais clic sans doute
regarde le dernier,peut être que le raisonnement est erroné ou pas complet

 

[cathodique]

Re : Données manquantes par rapport 2 dates (Dictionary)

Rebonjour Bebere,

Tu as fait un sacré travail. Dans les 2 colonnes résultats, des erreurs persistent.
La famille T1 figure dans la colonne des existants, alors qu'elle ne figure pas sur la feuille BD_test.
Pour la colonne des non existants, il y a un résultat juste qui concerne la famille RO1. Pour les autres familles, elles sont en effet inexistantes dans BD_test, mais elles doivent être exclues du résultat car la date du test est inférieure à la date d'acquisition. Je te joins le fichier commenté.

Merci beaucoup pour tes efforts.

 

[Bebere]

Re : Données manquantes par rapport 2 dates (Dictionary)

il y a des T1 à partir de ligne 4949
change les 2 lignes

L = BdRef.Range("A65536").End(xlUp).Row
L = BdTest.Range("A65536").End(xlUp).Row

comme elles étaient écrites ne donnait pas le nbre de lignes existantes
je pense qu'il faut marqué la ligne lorsque le résultat du test est bon
ce sera pour mercredi,demain je suis absent

edit:je pense qu'il faut tester date et famille d'abord,si la famille n'existe pas ne fait rien

 

[cathodique]

Re : Données manquantes par rapport 2 dates (Dictionary)

Bonsoir Bebere,

Je te remercie beaucoup. J'ai modifié par ceci L = BdRef.Range("A65536").End(xlUp).Row, comme tu me l'as demandé. Mais ça ne change rien au résultat.

Et, j'ai douté de la ligne de code que tu m'as demandé de modifié "Ltest = BdTest.Range("A" & Rows.Count).End(xlUp).Row",
j'ai ouvert mon dernier fichier joint, je ne sais pas ce qui s'est passé, j'ai trouvé plusieurs séries de lignes vides. Ce qui n'est pas normal. je pense que c'est la macro que j'avais utilisé pour réduire le fichier.

Je cherche à avoir seulement les données manquantes. Celles qui existent, c'est bon elle existent. Donc ne te casses pas la têtes pour celles qui existent.

Celles qui m’intéresse avec le dernier fichier sont les suivantes (les manquantes):
15/06/2005 - famille:T1 - sousfamille:IA/S2
15/06/2005 - famille:T1 - sousfamille:S2/PF
25/02/2006 - famille:RO1 - sousfamille:MD/S5

En effet, il y a des T1 à partir de la ligne 4630, ça tout simplement dire quelles existent bien dans la bd.

La difficulté vient des dates, plus que le code ne doit pas renvoyer une aberration. Une famille ne peut pas avoir de PV de test à une date avant son achat.

Je suis entrain de suivre un tuto sur les tableaux et essaie de mon côté.
Merci beaucoup, j'ai tout mon temps et de compte à rendre à personne, ça c'était avant.

ps: je remets le fichier sans les lignes vides.

 

[cathodique]

Re : Données manquantes par rapport 2 dates (Dictionary)

Bonsoir,
Mes remerciements à mapomme, laetitia90, gosselien, david84, bebere.
C’est grâce à vous que je suis parvenu à avoir un résultat satisfaisant. Mon code est assez long mais le résultat renvoyé est juste.
N’étant pas très à l’aise en codage et ne connaissant pas toutes les possibilités de vba. Mon approche a été des plus simplistes. J’ai aussi eu l’occasion d’apprendre que j’avais très mal énoncé mon problème.
Etant confronté au problème, je préfère vous exposer comment je suis parvenu au résultat.
Bd_Ref contient famille et sous-famille en colonne C et D, cette BD est à jour. Alors, toutes lignes de cette feuille doivent être enregistrées en BD_Test avec une date de test. Et chaque famille a une date d’acquisition.
Par contre BD_Test n’est pas à jour, le but est de connaitre qu’elle est la date, famille, sous-famille qui ne s’y trouve pas.
Grosso modo, j’ai pris dans des dictionnaires les dates de BD Test et les familles, sous-famille de Bd_Ref, j’ai redistribué ces dates au tableau famille, sous-famille, et obtenir un tableau dont le nombre de lignes = nb ligne initial*nb date.
Ensuite de ce tableau, j’ai éliminé les lignes dont la date de test >= à la date d’acquisition. En effet, il serait aberrant que le code me dise : test manquant pour la famille G1 en 1999, alors que celle-ci a été acquise en 2000. Par contre, elle existe bien dans BD_Ref, cette dernière a été mise à jour (elle contient toutes les familles).
Et enfin, on compare ce dernier tableau au dictionnaire Dic_Test et renvoyer le résultat dans un MsgBox.
J’ai effectué beaucoup de gymnastique car je ne maitrise pas bien vba et ses subtilités.
Je ne sais si ainsi c’est plus clair.
Si quelqu’un a un peu de temps pour améliorer le code ou en proposer un plus orthodoxe, je suis preneur.
En vous remerciant par avance.
Joyeux Noël.