Regroupement de Lignes filtrés

Le 22/10/2014 à 18:57

Bonjour,

J'ai un fichier mensuel qui contient des milliers de lignes et 12 colonnes : la 1ère étape de mon travail consisite à faire un filtre au niveau de la colonne C pour voir si j'ai une seule ligne pour la même clé, donc je reprends toute la ligne dans une autre feuille (pas de problème jusqu'au là), par contre si j'ai beaucoup de lignes pour la même clé, je dois faire les opérations suivantes :

  • Vérifier si les dates dans les colonnes G et H sont continus : G (ligne 4)= H (ligne 3) +1 etc...
  • Si les dates sont continus, j'utilise les formules de sous total pour calculer le max, min, somme ou moyenne suivant la colonne (voir ligne 2 au niveau de mon fichier)
  • Ensuite, je copie/colle le contenu des colonnes de A à E de la 1ere ligne filtrée dans la feuille résultat
  • En fin, je copie le résultat des colonnes de F à L affiché au niveau de la ligne 2 (résultat de sous total) et je le colle dans la feuille résultat
je passe à la ligne suivante et ainsi de suite...

PS: Si les dates ne sont pas continus, je prends toutes les lignes sans regrouper, ni faire des sous totaux

je vous joint un fichier exemple qui contient des données fictives : Pour être plus clair, j'ai fichier un exemple de filtres dans la feuille "Exemple" et son résultat attendu dans la feuille "Resultat", par contre ma base est dans la feuille "base"

Il s'agit d'une casse tête pour moi, je serai très reconnaissant si quelqu'un peut m'aider à automatiser au moins une partie de ce travail

Merci d'avance

Le 22/10/2014 à 21:17

Bonsoir,

Pas besoin de te casser la tête pour "regrouper des lignes filtrées" ....

Un Tableau Croisé Dynamique effectue le tout en quelques secondes ... pour toutes les clés ...

Ci-joint un fichier test ...

86test-morino.xlsx (296.11 Ko)
Le 22/10/2014 à 22:26

Bonsoir,

Merci pour votre retour et effort, cependant cette solution ne contrôle pas la continuité des dates début et fin des lignes qui ont la même clé

cette condition indispensable pour regrouper les lignes pour la même clé

je pense qu'il faut un petit code vba qui contrôle cette condition et regroupe les lignes si elle est vérifiée, si non il ne fait pas le regroupement

Merci en tout cas

Le 22/10/2014 à 22:31

Bonsoir,

Je n'ai pas fait attention à cette condition supplémentaire ...

Du coup, quelles sont les règles qui régissent la "continuité des dates de début et de fin" ...?

Est-ce-que tu as déjà formulé, rédigé ou codé ces règles ?

Le 22/10/2014 à 23:50

Re,

Pour bâtir la "continuité des dates" ... du moins de la façon dont je la comprends, tu trouveras trois formules appliquées à la base de données ... une fois la base de donéées triée par clé et par date ...

Si la formule de la Colonne O produit le test dont tu as besoin ...

En ajoutant le test de "continuité des dates" au TCD, on trouve automatiquement les résultats escomptés ...

Dis-moi si cela correspond à ton attente ...

23test-morino.xlsx (293.64 Ko)
Le 23/10/2014 à 00:56

Bonsoir le fil, bonsoir le forum,

Une autre proposition avec le code commentée ci-dessous :

Sub Macro2()
Dim BA As Object 'déclare la variable BA (onglet BAse)
Dim RE As Object 'déclare la variable RE (onglet REsultat)
Dim DL As Long 'déclare la variable DL (Dernière Ligne)
Dim TC As Variant 'déclare la variable TC (Tableau de Cellules)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Long 'déclare la variable I (Incrément)
Dim TOS As Variant 'déclare la variable TOS (Tableau des Occurrence Sans doublon)
Dim TNO As Variant 'déclare la variable TNO (Tableau du Nombre des Occurrences)
Dim DEST As Range 'déclare la variable DEST (cellule de DESTination)
Dim LI As Long 'déclare la variable LI (LIgne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim TCF As Variant 'déclare la variable TCF (Tableau des Cellules Filtrées)
Dim J As Integer 'déclare la variable J (Incrément)
Dim D1 As Date 'déclare la variable D1 (Date 1)
Dim D2 As Date 'déclare la variable D2 (Date 2)
Dim NL As Integer 'déclare la variable NL (Nombre de Ligne)

Set BA = Sheets("base") 'définit l'onglet BA (à adapter)
Set RE = Sheets("resultat") 'définit l'onglet RE (à adapter)
DL = BA.Cells(Application.Rows.Count, 3).End(xlUp).Row 'définit la dernière ligne éditée de la colonne 3 (=C) de l'onglet BA
TC = BA.Range("A4:L" & DL) 'définit le tableau de cellules TC
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 1 To UBound(TC, 1) 'boucle sur toutes les cellules du tableau de cellules TC
    D(TC(I, 3)) = D(TC(I, 3)) + 1 'alimente le dictionnaire D
Next I 'prochaine cellule de la boucle
TOS = D.keys 'récupère dans le tableau TOS la liste du dictionnaire sans doublon
TNO = D.items 'récupère dans le tableau TNO le nombre de fois que l'occurrence TOS(I) apparaît dans la base
For I = 0 To UBound(TOS) 'boucle sur tous les éléments du tableau TOS
    Set DEST = RE.Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0) 'définit la cellule de destination DEST
    LI = BA.Columns(3).Find(TOS(I), BA.Range("C1"), xlValues, xlWhole).Row 'définit la ligne de la première occurrence de TOS(I)
    If TNO(I) = 1 Then 'condition 1 : si TOS(I) n'apparaît qu'une seule fois
        BA.Cells(LI, 1).Resize(, 12).Copy DEST 'copie la ligne dans DEST
    Else 'sinon (si TOS(I) apparaît plusieurs fois)
        BA.Range("N1") = "Clé" 'écrit "Clé"en N1
        BA.Range("N2") = "'" & TOS(I) 'écrit l'occurrence TOS(I) en N2
        'récupère par filtre élaboré les lignes de TOS(I) dans la cellule N3
        BA.Range("A3:L" & DL).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=BA.Range("N1:N2"), CopyToRange:=BA.Range("N3:Y3"), Unique:=False
        Set PL = BA.Range("N1").CurrentRegion 'définit la plage PL
        Set PL = PL.Offset(3, 0).Resize(PL.Rows.Count - 3) 'redéfinit la plage PL (uniquement les données)
        TCF = PL 'définit le tableau TCF
        For J = 1 To UBound(TCF) - 1 'boucle 2 : sur tous les lignes du tableau TCF (sauf la dernière)
            D1 = DateSerial(Year(TCF(J, 8)), Month(TCF(J, 8)), Day(TCF(J, 8))) 'définit la date D1
            D2 = DateSerial(Year(TCF(J + 1, 7)), Month(TCF(J + 1, 7)), Day(TCF(J + 1, 7))) 'définit la date D2
            If D1 + 1 <> D2 Then 'condition 2 : si les dates ne se suivent pas
                PL.Copy DEST 'copie la plage PL dans DEST
                BA.Range("N1").CurrentRegion.Clear 'efface le tableau du filtre élaboré
                GoTo suite 'va à l'étiquette "suite"
            End If 'fin de la condition 2
        Next J 'prochaine ligne de la boucle 2
        NL = PL.Rows.Count 'définit le nombre de ligne de la plage PL
        PL.Rows(1).Copy DEST 'copye la ligne 1 du tableau dans DEST
        DEST.Offset(0, 7).Value = PL(NL, 8) 'remplace la date de fin de la première ligne par la dernière date de fin de la plage PL
        'remplace le nombre de la première ligne par somme des nombre de la plage PL
        DEST.Offset(0, 8).Value = Application.WorksheetFunction.Sum(Range(PL(1, 9), PL(NL, 9)))

        'j'ai rien fait pour le taux car je ne savais pas quoi faire. J'ai laisser le premier taux....

        'remplace le brut de la première ligne par somme des brut de la plage PL
        DEST.Offset(0, 10).Value = Application.WorksheetFunction.Sum(Range(PL(1, 11), PL(NL, 11)))
        'remplace le net de la première ligne par somme des net de la plage PL
        DEST.Offset(0, 11).Value = Application.WorksheetFunction.Sum(Range(PL(1, 12), PL(NL, 12)))
        DEST.Resize(1, 12).Interior.ColorIndex = 24 'colore la ligne de violet
    End If 'fin de la condition 1
suite: 'étiquette
Next I 'prochain élément de la boucle 1
End Sub
Le 23/10/2014 à 10:04

Bonjour,

Ma petite contribution à méditer.

Cdlt.

74regroupement-de-lignes-filtres.xlsb (204.03 Ko)
Le 23/10/2014 à 16:49

Bonjour tout le monde,

Merci beaucoup pour toutes les personnes qui ont fait l'effort et tenter de m'aider

Après vérification de toutes les solutions, la plus convenable est celle proposée par ThauThème

Les tableaux croisés dynamiques ne résoulvent pas le problème puisqu'ils regroupent même les lignes qui ne vérifient pas la condition de continuité des dates

De nouveau Merci

Le 30/10/2014 à 17:26

Bonsoir ThauThème ,

Je reviens vers vous pour voir si possible de prendre en compte cette remarque supplémentaire à savoir :

Dans pas mal de cas, pour la même clé, j'ai beaucoup de lignes dont certains sont continus et d'autres non, est-il possible de regrouper comme même les lignes en continuité et garder les autres pour cette clé ?

Par exemple si, j'ai 4 lignes pour la même clé : les 3 premières lignes sont en continuité de dates, par contre la 4ème ligne ne l'ai pas, donc comme résultat je dois avoir 2 lignes ; la 1ère est le résultat du regroupement des 3 lignes cependant la 2ème est l'image de la 4ème ligne

'j'ai rien fait pour le taux car je ne savais pas quoi faire. J'ai laisser le premier taux....' : Pour le taux, c'est la moyenne des lignes à regrouper

Je vous remercie énormément si vous pouvez m'aider sur ce point ?

Merci d'avance

Rechercher des sujets similaires à "regroupement lignes filtres"