Réduction de 12 lignes en 1 ligne avec nouvelles dates - de mois à année

Bonjour,

Je ne sais pas bien quel vocabulaire excel je dois emprunter pour décrire ma question.

Je le fais donc avec un exemple.

J'ai un fichier de 4000 personnes. Ce fichier contient pour chaque personne 1 ligne par mois avec un montant

Donc si X a été présent 12 mois, il a 12 lignes. Mon fichier est donc d'environ 48.000 lignes.

Je dois transformer ce fichier "mensuel" en fichier annuel. Mais en plus quand un des renseignements n'est pas identique je dois scinder le fichier et donc il est possible que pour des personnes j'ai plusieurs lignes mais je dois avoir pour chaque période la date de début et la date de fin.

Je voudrais donc réduire ces 12 lignes à 1 avec la 1er nouvelle date de début et la dernière date de fin et donc pour certains avoir la ou les lignes avec le renseignement spécial et avoir le total. Donc la somme des 12 mois.

Je ne sais pas s'il y a moyen de travailler avec une fonction ou s'il y a un "truc" manuel mais vous imaginez que passer 48.000 lignes en revue n'est pas possible.

Je joins un fichier avec deux exemples et le résultat à obtenir est sur la droite.

Ce serait super sympa si vous saviez m'aider.

T.G.

Bonjour,

essayez ceci

Cdlt

Bonsoir,

Merci pour votre réponse.

Mais je ne vois pas ce que je dois faire ?

TG

Bonsoir Tooiesgamer,

Bonsoir tout le monde

C'est pas moi qui t'a proposé la solution,je pense que tu dois faire ceci

tu copie toute ta base,(les 4000 lignes comme tu dis) tu sélectionne A2 du tableau de gauche ,tu colles , tu cliques sur le bouton ,si tout va bien c'est à dire pas de beug dans la macro

le résultat apparaît dans le tableau de droite(j'ai fais un test en effaçant manuellement le tableau de droite et en cliquant sur le bouton ;le résultat

est le même que celui que tu as donné)

Cdlmt

Re bonsoir

NB: pas besoin d'effacer le tableau de droite manuellement chaque fois

Moi je l'ai fait seulement pour tester mais quand tu cliques sur le bouton la macro efface les anciennes valeurs

et inscrit les nouvelles

Cdlmt

Bonsoir,

Merci pour votre aide. Cela m'aide énormément.

J'ai noté qu'il fallait bien aligner les noms et les dates dans l'ordre. Si une période s'intercale, cela prend deux ois la même date de départ.

Ce n'est pas très grave. Je vais tester sur 10000 lignes. J'en ai 48000 pas 4000 ! ;-)

Maintenant, j'aimerais bien savoir quelle fonction vous utilisez car ici c'est un modèle de fichier mais j'en aurai d'autres du mème genre.

Personne, mois d'une année (jusqu'à 12), item 1, item, 2 jusqu'à 5 et somme des valeurs dans la dernière colonne.

Donc, au lieu de vous faire tourner vos méninges, je le ferais bien de mon côté.

Encore merci je reviens vers vous après le test de grande ampleur.

TG

Bonjour,

Je viens de tester sur 4.000 lignes et ce la fonctionne ! Bravo et merci pour votre aide.

C'est une excellente nouvelle.

Par contre, je devrais pouvoir faire le même types d'opération avec bien plus de colonnes et le même nombre de lignes.

Comment puis-je procéder pour ne pas vous ennuyer chaque fois ;-) ?

Quelles fonctions utilisez vous dans la macro ?

Merci d'avance.

TG

Bonjour,

"je devrais pouvoir faire le même types d'opération avec bien plus de colonnes et le même nombre de lignes."

Il n'y a pas de problème, mais cela soulève des questions.

- le nombre de colonnes sera-il toujours le même?

-les "dates in" et "dates out" seront elles-toujours à la même place, colonne D et E?

-Pour la période qui sera retenue entre 2 changements de "Noms" ou de "Services", parmi ces colonnes supplémentaires, quelles seront les informations à reporter dans le nouveau tableau? celles du début "date in", celles de "Date out"?

Il serait préférable que vous mettiez un bout de votre fichier, (quelques lignes suffisent), sans données confidentielles mais avec toutes les colonnes. et de bien préciser s'il y a des particularités?

Cdlt

Merci Arturo,

Je vais mettre le fichier le plus complet dont je dispose ce soir.

En fait, comme je ne sais pas si vous vous basez sur la premiere colonne avec une date ou la seconde, je vais la laisser mais c'est un peu redondant avec la seconde date - "date in".

Je pense que cela peut aller jusqu'à 10 colonnes avec des renseignements différents.

TG

Bonjour Arturo83, Abdu,

Bonjour à tous,

Comme je le disais hier suite à votre dernier message, je poste un autre fichier plus complet.

La macro fonctionne très bien mais je sais que j'aurai besoin de ce type de réduction de fichiers assez fréquemment sur plusieurs fichiers.

D'où ma demande de pouvoir reproduire ce type d'opérations par moi-même. Bien sur... si on sait me fait le travail encore pour cette fois-ci je sui preneur ;-) ;-) Mais je devrai inévitablement devoir le refaire.

Le principe est toujours le même. Il s'agit de réduire les lignes identiques à 1 ligne avec les mêmes renseignements.

Si dans une colonne de D jusqu'à P, il y a un changement, peut importe lequel, la ligne doit apparaître distinctement avec ce changement avec la période concernée et la somme des heures pour la dite ligne.

Il faut savoir que j'ai des fichiers avec 5.000 produits (mat) et sur 12 mois. Je pourrais donc arriver à 60.000 lignes à réduire.

Donc, si vous savez me faire le fichier.... ce serait top. Et me montrer les fonctions ou la manière de faire top de chez top !

J'ai mis à droite à quoi cela devrait correspondre pour les premiers produits

D'avance merci pour votre aide.

TG

Bonjour,

Si dans une colonne de D jusqu'à P, il y a un changement,

Ne serait pas plutôt de A à P? ou du moins le matricule doit être pris en compte (pas les changements de dates)

Oui, j'avais résumé.

En fait idéalement, chaque matricule devrait avoir 1 ligne du 01/01 au 31/12. Mais vu que d'autres paramètres changent parfois, on peut avoir plusieurs lignes pour 1 même matricule mais en précisant bien la période.

On peut donc le voir comme tu le dis.

Bonjour,

Voilà en espérant qu'il n'y ait pas d'erreur.

Cdlt

Bonsoir Arturo,

J'ai testé et malheureusement cela n'est pas le résultat souhaité.

Il ne prend pas toutes les lignes mais simplement les premiers matricules. J'avais mis quelques exemples et quelques résultats mais j'ai 4000 personnes et 48000 lignes.

Ensuite, si tu regardes les lignes, ils devaient dans certains cas mettre 1/1 au 31/12 or il met chaque fois 01/01 et reproduit chaque mois.

Par exemple pour le198370 il ne prend pas 31/12 ...

Bonjour,

Effectivement quelques petites erreurs trouvées, voici le fichier corrigé, ça me semble correct, n'hésitez pas à me dire s'il y a encore des anomalies que je n'aurai pas vu.

Cdlt

Bonsoir Arturo,

J'ai testé sur l'exemple et a priori cela me parait bon.

Je vais tester sur le fichier dans son ensemble... 48.000 lignes.

Je reviendrai vers toi pour mes remarques.

Mais en tout cas, déjà, je te remercie pour ton aide.

Par contre, quelles formules ou fonctions utilises-tu ? Si je veux le reproduire...cela m'intéresse.

Cordialement.

TG

Bonjour,

J'utilise 1 seule formule en colonne R que je supprime à la fin

=SI(ET(A2=A1;(D1:P1)=(D2:P2));0;1)

Le principe:

-on fait un ET logique entre, d'une part:

-les matricules, on compare le matricule de la ligne 2 avec le précédent,

-et d'autre part, on compare la ligne 2 des colonnes D à P avec les colonnes D à P de la ligne précédente. Si l'un des 2 tests renvoi "faux", cela veut dire qu'il y a des écarts, alors je mets 1 en colonne R sinon 0. Ainsi je recopie toutes les lignes où il y a un 1, pour les cellules contenant un 0, je sais qu'il va falloir additionner les montants jusqu'à la prochaine rencontre d'un 1 et récupérer la date de fin du dernier 0.

En espérant avoir été clair.

Cdlt

Bonjour Arturo,

J'ai testé et c'est bon ;-)

J'ai commencé à essayer de créer la macro.... bon je rame un peu mais je ne vais pas lâcher !

En attendant, pourrais-je te demander d'insérer deux colonnes dans le fichier et me refaire la macro ?

Je pourrai alors essayer de mon côté et arriver au résultat final que je comparerai avec le tien.

Si ce n'est pas possible ce n'est pas grave, cela m'aide déjà beaucoup.

Le reste est inchangé, .... 48.000 lignes et la totalisation de la dernière colonne.

Encore merci pour ton aide.

TG

Bonjour,

Voici le fichier, j'ai commenté chaque ligne de code pour vous aider à mieux l'appréhender.

J'aimerai bien connaître le temps d'exécution pour les 48000 lignes.

Cdlt

Bonjour Arturo,

Encore merci pour le temps passé à m'aider.

J'ai lancé la macro sur un fichier réduit et cela ne donne pas les bons résultats. J'ai mis un exemplaire du résultat en fichier.

Je l'avais d'abord essayer sur le fichier massif 40000 LIGNES et il bloque avec le passage suivant en rouge

Sub Reduire()

'Déclaration des variables
Dim DerLig As Long, i As Long, j As Long, Lig_Dest As Long, DerCol As Long
Dim Total As Double

Application.ScreenUpdating = False 'pour éviter les rafraîchissemnts de l'écran et augmenter la vitesse d'exécution
debut = Timer 'enregistre l'heure de début de l'exécution du code
DerLig = Range("A" & Rows.Count).End(xlUp).Row 'dernière ligne du tableau de données
DerCol = Range("A1").End(xlToRight).Column 'dernière colonne du tableau de données

Range(Cells(2, "U"), Cells(DerLig, "AM")).Clear ' on efface la totalité des précédents résultats
Range("T2").FormulaArray = "=IF(AND(RC1=R[-1]C1,(R[-1]C4:R[-1]C18)=(RC4:RC18)),0,1)" 'formule matricielle validée en excel avec CTRL + SHIFT + ENTREE
Range("T2").AutoFill Destination:=Range("T2:T" & DerLig) 'on recopie la formule jusquà la dernière ligne
Range("T2:T" & DerLig).Value = Range("T2:T" & DerLig).Value 'on remplace les formules par les valeurs

Lig_Dest = 2 'première ligne vide du tableau de destination du résultat
For i = 2 To DerLig 'de la première ligne à la dernière ligne du tableau de données
If Cells(i, "T") = 1 Then 'si la valeur=1 alors,
j = i 'on applique la valeur de i à la variable j
Total = Cells(j, "S") 'on met le nombre d'heures dans la variable "Total"
Range(Cells(Lig_Dest, "U"), Cells(Lig_Dest, "AM")).Value = Range(Cells(i, "A"), Cells(i, "S")).Value 'on copie la ligne du tableau des valeurs dans dans la ligne du tableau de destination
Do While Cells(j + 1, "T") <> 1 And j + 1 <= DerLig ' tant que les lignes suivantes de la colonne T seront égales à 0
Total = Total + Cells(j + 1, "Q") 'alors on ajoute au total le nombre d'heures de chaque ligne dont la cellule en colonne T = 0
j = j + 1 'on incrénete j pour tester la ligne suivante
Loop ' on recommence le test jusq'à ce que la cellule en colonne "T" soit égale à 1 ou vide
Cells(Lig_Dest, "V") = Format(Cells(i, "B"), "m/d/yyyy") 'on applique le format "Dtae" sur les dates de début et de fin
Cells(Lig_Dest, "W") = Format(Cells(j, "C"), "m/d/yyyy")
Cells(Lig_Dest, "AM") = Total 'on recopie le total
i = j 'on applique la valeur de J à i
Lig_Dest = Lig_Dest + 1 ' on incrémente la ligne de destination
End If
Next i 'on recommence le cycle avec les lignes suivantes
With Range(Cells(1, "U"), Cells(1, "AM")) 'sur la ligne d'entête du tableau de destination
.Value = Range(Cells(1, "A"), Cells(1, "S")).Value 'on y recopie les titres du tableau de données
.Interior.Color = RGB(55, 96, 145) 'on y applique la couleur bleue foncée
.Font.Color = RGB(255, 255, 255) 'avec la police en blanc
End With
Range("U1:AM" & Range("A" & Rows.Count).End(xlUp).Row).Borders().Weight = xlThin 'on applique un quadrillage
Columns("T").ClearContents 'on efface la colonne T
MsgBox "Durée d'exécution; " & Timer - debut & " sec" ' affiche le temps d'exécution
End Sub