XL 2010 Compléter colonnes selon fichier de référence

KIM

XLDnaute Accro
Bonjour le forum, bonjour les ami(e)s,
Votre réactivité dans ce forum m'a rendu beaucoup de services. Je reviens ainsi vers vous pour solliciter votre aide. J'ai un fichier de référence (CatGrpRef.xls) pour les catégories (3) et groupes (4).
- Les 3 catégories sont liées
- Chaque groupe est positionné à "Oui" selon le contenu des 3 catégories.
- Les données des catégories Cat_1 et Cat_2 sont figées Col B et C
- CAT_1 est obligatoire et ne peut pas être vide
-CAT_2 n'est pas obligatoire (vide)
- CAT_3 n'est pas obligatoire, peut être conforme aux données de la col D pour positionner les groupes à Oui sinon saisie libre. "*" veut dire vide ou saisie libre différente de la liste proposée.
Je reçois régulièrement un fichier de quelques milliers de lignes du type CompléterSelonCritères.xls. Je souhaite compléter les colonnes L, M, N, O à "Oui" et R pour les commentaires selon les catégories col I, J et K par les données correspondantes du fichier de référence (CatGrpRef.xls).
Les anomalies à traiter : Si CAT_1 et/ou CAT_2 ne sont pas conformes au tableau de référence :
-Il faut noter dans Commentaires (Col R) : CAT_2 INCONNU (par exemple, ligne 17) et appliquer la règle pour les groupes comme si CAT-2 est vide.
- Si Cat_1 est erronnée, c-à-d n'existe pas dans le référentiel (Col B), mettre "X" pour tous les groupes et dans Commentaires (Col R) : CAT_1 INCONNU (par exemple, ligne 24)
Ci-joints CatGrpRef.xls et CompléterSelonCritères.xls
Je vous remercie par avance de votre aide.
Bien cordialement KIM
 

Pièces jointes

  • CatGrpRef.xls
    40.5 KB · Affichages: 42
  • CompleterSelonCritères.xls
    51.5 KB · Affichages: 37

KIM

XLDnaute Accro
Bonjour Jean marie,
Bonjour Bebere,
Jean marie,
J'ai regardé ta macro, comme d'habitude claire et commentée. Elle ne traite pas tous les erreurs de Cat1. On peut corriger certaines erreurs avant de comparer avec le fichier de référence. Pour Cat1, transformer Cat1 en majuscule, et supprimer tous les espaces au début et à la fin de chaque cellule.
Quand Cat2 est erronée, à traiter comme si Cat2 est vide et mettre dans commentaires Cat 2 "xyz par ex" Erronée
le traitement des autres erreurs est complexe. Dans mon fichier de référence j'ai au plus de 50 Cat1 et une liste de plus de 200 Cat2, sans oublier Cat3. Il y a beaucoup d'erreurs de saisie dans les fichiers à traiter, des tirets, des espaces, casse non respectée, mots coupés ou transformés, etc. Ainsi un dictionnaire externe pour chaque catégorie peut facilement corriger ces erreurs. La 1è col de ce fichier (cat erronée) peut être alimentée par une macro et la 2è col (cat valide) sera saisie manuelle.
Ensuite une macro pour remplacer les codes erronées par les codes valides dans chaque fichier traité.
Suivra une macro pour compléter les groupes et commentaires.
Merci encore
KIM
 

Pièces jointes

  • CompleterSelonCritères_Chti160.xlsm
    41.7 KB · Affichages: 31

Bebere

XLDnaute Barbatruc
bonjour Kim,Jean Marie
voilà un essai pour les erreurs,code module3,résultat feuil2 avec le nom,le nbre et les lignes
dans ce même code je pense appliquer les corrections à effectuer pour ensuite traiter les oui
l'idée avoir 2 tableaux a et abis(données listeacompléter) et corriger abis si une erreur est trouvée
 

Pièces jointes

  • CompleterSelonCritères_v4.xlsm
    77.4 KB · Affichages: 38

ChTi160

XLDnaute Barbatruc
Bonjoru KIM
Bonjour Bebere ,Le Fil ,Le Forum
ouppss pas évident quelques question
les Codes des Cat1 à Cat3 sont t'ils de vrais code ?
Pourrais tu ? si Oui , mettre cette liste des Cat1 à Cat3
AAAA pour cette Cat1 on peut avoir si j'ai bien compris
" AAAA" "AAAA " avec un espace devant ou derrière
au milieu "AA AA" ou "A AAA" ou "AAA A" ou alors un tiret au milieu "AA-AA" ou "A-AAA" ou AAA-A"
ou des minuscules "aAAA ,"AaAA , "AAaA , "AAAa avec bien sur des espaces et des tirets Lol
IDEM exemples pour BCDA-BF , BC-DABF ,BCDA BF , etc etc
Peut on envisager que "AAA" et un "AAAA" auquel on a oublie un "A" ? avec bien sur des espaces et des tirets .
Peux tu nous éclairer ?
Merci Par avance
Amicalement
Jean Marie
 

KIM

XLDnaute Accro
Bonjour Bebere, Bonjour Jean Marie et le forum,
@Bebere,
Le code des erreurs traite seulement Cat1. Liste OK par contre est-il possible d'afficher les erreurs en ligne et non en colonne (voir fichier ci-joint) car le fichier traité peut faire quelques centaines de lignes.

@jean Marie,
Dans le fichier de référence (exemple ci-joint), les codes sont figés. Les codes dans le fichier à traiter doivent respecter à l'identique les codes du fichier de référence (casse, espace, etc.).
"AAA", "aAAA", "AA AA", " AAAA " etc. sont des erreurs non identiques à "AAAA"
Idem "BC-DA-BF", "B-CDABF", "BCDA_BF", etc. sont des erreurs non identiques à "BCDA-BF"
etc.
Cat3 est un cas complexe car on permet la saisie libre référencé par "*" avec des cas particuliers.

Merci d'avance
KIM
 

Pièces jointes

  • CompleterSelonCritères_v4.xlsm
    80.9 KB · Affichages: 31
  • CatGrpRef.xls
    40.5 KB · Affichages: 34

KIM

XLDnaute Accro
Bonjour Jean Marie, Bebere et le foum,
AaaA devient AAAA.
AAAA correspond à AAAA Cat1 du le fichier de référence. OK
Il faut ensuite traiter Cat2 ensuite Cat3 du fichier à traiter en les comparant avec le fichier de référence pour positionner les groupes.
Il faut Cat1, Cat2 et Cat3 du fichier à traiter correspondent au fichier de référence pour positionner les groupes.
Je suis disponible pour plus de précision si nécessaire.
Merci d'avance
KIM
 
Dernière édition:

KIM

XLDnaute Accro
Bonjour Bebere, Jean Marie et le foum,
Je vais essayer de vous donner plus de détails suit à nos discussions ci-dessus.
1/ Traitement des erreurs
- Dans le fichier à traiter, Cat1 et Cat2 doivent être conformes et identiques, sans faute, (respect de la casse, sans espace, etc) à Cat1 et Cat2 du fichier de référence.
-Cat1 est obligatoire, non vide ni espace.
-Cat2 peut être vide mais pas d'espace,
- Pour Cat3, la saise libre est possible ( "*") par contre je dois corriger des Cat3 qui existent dans le fichier de réfrence et qui sont saisies avec des fautes dans le fichier à traiter.
2/ Compléter les groupes
Suite au traitement des erreurs dans le fichier à traiter, Cat1 et Cat2 sont corrects et conformes au fichier de référence, on peut compléter les groupes dans le fichier à traiter selon les données du fichier de référence.

Merci de votre aide
Bon dimanche
KIM
 

Bebere

XLDnaute Barbatruc
bonsoir Kim,Jean Marie,le forum
Kim voilà où j'en suis avec Cat1 +2 fonctions
une qui contrôle la chaîne par rapport à la référence
et une pour savoir si le tableau est initialisé
cat2 est commencé


Code:
Option Explicit
Option Compare Text
Public a, b

Sub ErreurCat1()    'donne le nombre et les lignes
    Dim d1 As Dictionary, d As Dictionary, aa, bb, i As Long, Li As Long
    Dim c As Byte, L As Long, tblA(), z As Long, item, autre
    Dim clé, clébase, indice, ligne, tmp    ' As Variant
    Dim m1 As String, m2 As String, mm1, mm2, pos, crit
    Feuil3.Cells.Clear
    a = Feuil2.Range("I11:I" & Feuil2.Range("I65536").End(xlUp).Row)
    b = Feuil2.Range("I11:I" & Feuil2.Range("I65536").End(xlUp).Row) 'tableau corrigé pour cat1

    aa = Array("", " ", "AA*", "XYZ*", "BCD*", "autre")
    bb = Array("vide", "espace ", "Faute AAAA", "Faute XYZ-PRT", "Faute BCDA-BF", "Faute autre")
    c = 1
    For i = LBound(aa) To UBound(aa)
        Select Case i
        Case 0
            crit = aa(i)

        Case 1
            crit = aa(i)

        Case 2    'AAAA
            crit = aa(i)

        Case 3    'XYZ-PRT
            crit = aa(i)

        Case 4
            crit = aa(i)
        Case 5    'autre
            z = 0
            For L = 1 To UBound(a, 1)
                If Len(a(L, 1)) > 1 Then
                    If UCase(a(L, 1)) Like "AAA*" Or UCase(a(L, 1)) Like "XYZ*" Or UCase(a(L, 1)) Like "BCD*" Then
                    Else 'si plusieurs autres,écrire direct dans la feuille
'                        ReDim Preserve autre(z): autre(z) = a(L, 1) & "-" & i + 10: z = z + 1
                        autre = a(L, 1) & "-" & i + 10
                        crit = a(L, 1)
                    End If
               
                End If
            Next
        End Select
        Set d1 = New Dictionary
        For L = 1 To UBound(a, 1)
            If a(L, 1) Like crit Then
                clébase = crit
                clé = clébase
                indice = 1
                Do While d1.Exists(clé)
                    clé = clébase & indice
                    indice = indice + 1
                Loop
                d1(clé) = L
            End If

        Next L
        clébase = crit
        clé = clébase
        indice = 1
        Do While d1.Exists(clé)
            ligne = d1(clé)
            Select Case i
            Case 0    'vide
                ReDim Preserve tblA(0 To 1, 0 To z)
                tblA(0, z) = "I" & ligne + 10
                tblA(1, z) = bb(i)
                b(ligne, 1) = "INCONNU"
                z = z + 1

            Case 1    'espace
                ReDim Preserve tblA(0 To 1, 0 To z)
                tblA(0, z) = "I" & ligne + 10
                tblA(1, z) = bb(i)
                b(ligne, 1) = "INCONNU"
                z = z + 1

            Case 2    'AAAA
                If MajMin("AAAA", CStr(a(ligne, 1))) = False Then
                    ReDim Preserve tblA(0 To 1, 0 To z)
                    tblA(0, z) = "I" & ligne + 10
                    tblA(1, z) = bb(i)
                    b(ligne, 1) = "AAAA"
                    z = z + 1
                End If

            Case 3    'XYZ-PRT
                If MajMin("XYZ-PRT", CStr(a(ligne, 1))) = False Then
                    ReDim Preserve tblA(0 To 1, 0 To z)
                    tblA(0, z) = "I" & ligne + 10
                    tblA(1, z) = bb(i)
                    b(ligne, 1) = "XYZ-PRT"
                    z = z + 1
                End If

            Case 4    'BCDA-BF
                If MajMin("BCDA-BF", CStr(a(ligne, 1))) = False Then
                    ReDim Preserve tblA(0 To 1, 0 To z)
                    tblA(0, z) = "I" & ligne + 10
                    tblA(1, z) = bb(i)
                    b(ligne, 1) = "BCDA-BF"
                    z = z + 1
                End If
               
            Case 5 ' autre
                If autre <> "" Then 'If HasBounds(autre) Then
'                For Li = 0 To UBound(autre)
                ReDim Preserve tblA(0 To 1, 0 To z)
                tblA(0, z) = "I" & Mid(autre, InStr(autre, "-") + 1)
                tblA(1, z) = Mid(autre, 1, InStr(autre, "-") - 1)
                z = z + 1
'                Next Li
End If

            End Select

            clé = clébase & indice
            indice = indice + 1
        Loop
        If HasBounds(tblA) Then
            If [B1] = "" Then Li = 1 Else Li = [B2000].End(xlUp).Row + 1
            Feuil3.Cells(Li, 1) = UBound(tblA, 2) + 1    'nbre
            Feuil3.Cells(Li, 2) = tblA(1, 0)
            Li = Li + 1
            For L = 0 To UBound(tblA, 2)
                Feuil3.Cells(Li + L, 2) = tblA(0, L)
            Next
            Erase tblA
            z = 0: autre = ""
        End If
    Next i

End Sub
Code:
Function MajMin(x As String, y As String) As Boolean    'x=ref,y à tester
    Dim i As Long, a, b, c As Byte

    For i = 1 To Len(y)
        a = Mid(x, i, 1)
        b = Mid(y, i, 1)
        If Asc(a) = Asc(b) Then
            c = c + 1
        End If
    Next i
    If c = Len(x) Then MajMin = True ' Else MajMin = False

End Function


Public Function HasBounds(myArray As Variant)
Dim lb, ub
    lb = Empty: ub = Empty
    On Error Resume Next
    lb = LBound(myArray, 1)
    ub = UBound(myArray, 1)
    On Error GoTo 0
    HasBounds = Not (IsEmpty(lb) Or IsEmpty(ub))
End Function    'Tom Ogilvy
 

KIM

XLDnaute Accro
Bonsoir Bebere, Jean Marie et le forum,
Merci Bebere pour ce nouveau traitement de Cat1. Avant de passer à Cat2 :
1- Je viens de tester le code, aucun message d'erreur ni correction, ni message. Rien ne se passe.
2- Les références utilisées dans les fichiers exemple sont différentes des données réelles, pour cause de confidentialité;
2- A la lecture du code, je me permets de poser quelques questions pour mieux comprendre :
a- Je ne trouve aucun appel au fichier de référence CatGrpRef.xls.
Est-ce que les catégories sont initialisés en dur dans le tableau aa ?
J'ai au moins 50 références de Cat1.
N'est-il pas possible d'alimenter dynamiquement le tableau aa directement via le fichier de référence par la lecture de la col B de CatGrpRef.xls.

PS : Je viens d'exécuter la macro de nouveau j'ai le message suivant dans fonction MajMin, ligne If Asc(a) = Asc(b) Then : Argument ou Appel de procédure incorrect

Je compte sur votre aide. et vous en remercie d'avance.
KIM
 
Dernière édition:

Bebere

XLDnaute Barbatruc
bonjour Kim
ce n'est qu'un essai pour savoir si le résultat est bon de ton côté
mis en dur ,c'est plus lisible pour mise au point
Je te prépare un fichier plus complet avec cat1 et cat2
je pense faire comme suit
1 aller chercher les références les mettre dans une feuille ref
2 traiter les 3 cat sans doublons et mettre dans la feuille ref
3 erreurs cat1 et corrections
4 erreurs cat2 et corrections
5 cat3 idem
6 pour finir les groupes
je pense qu'i n'est pas besoin de répéter à chaque fois le cycle complet
et si oui il suffit d'appeler la procédure suivante
 

KIM

XLDnaute Accro
Bonjour Bebere,
Bonjour Jean Marie et le forum,
L'ordre des actions est logique. Par contre j'essaye de comprendre les actions :
1 et 2/ Récupérer les références Cat1, Cat2 et Cat3, sans doublon, dans une nouvelle feuille Ref du fichier CompleterSelonCritères.xls, une bonne idée, mais
Est-ce que chaque Cat peut être dans une colonne à part ? C'est plus pratique pour contrôler et tester.
3 et 4 / Traiter les erreurs classiques en dur : vide, espace, double espace, espace au début et/ou à la fin de la cellule, mise en majuscule Cat1, etc OK. Par contre comment traiter les erreurs basées sur l'imagination humaine : les abréviations, les erreurs syntaxiques, etc. la liste est longue ?

Merci encore
KIM
 

Bebere

XLDnaute Barbatruc
Kim
met des exemples avec la correspondance que l'on se rende compte de quoi on parle
envoye un email s'il le faut
ou y a t'il moyen d'apparenter l'erreur avec la référence, dommage qu'il n'y a pas un code
9à intéresse aussi Jean Marie,je pense
la fonction MajMin compare les codes asc caractère par caractère
 

KIM

XLDnaute Accro
Bebere, Jean Marie,
Ci-joint une liste des erreurs rencontrés pour Cat1 et Cat2, correspondance entre le code erroné et le code correct.

Je vous remercie de votre patience.
Merci encore
KIM
 

Pièces jointes

  • Dict_Cat1_Correction.xls
    29 KB · Affichages: 28
  • Dict_Cat2_Correction.xls
    31 KB · Affichages: 30

Discussions similaires

Réponses
2
Affichages
113

Statistiques des forums

Discussions
311 720
Messages
2 081 902
Membres
101 834
dernier inscrit
Jeremy06510