Effacer les valeurs dans une cellule en doublon sans supprimer la ligne

Bonjour le forum,

J’ai une nouvelle fois besoin de vos précieux et pertinents conseils. J’ai parcouru les topics et je ne vois pas de réponses à ma question.

Je vous explique mon problème.

Je travaille sur des énormes fichiers qui sont transmis par un software externe de gestion des horaires. Je ne suis pas loin de 900.000 lignes dans ce cas.

Je dis cela parce que le fichier source ne sait pas être adapté par cette société. Je dois donc le retravailler sur un point bien précis.

Mon fichier contient au minimum une ligne par jour et par personne. En principe, il y a un code par jour avec une valeur. Mais il se peut que sur un même jour, j’ai plusieurs codes avec en regard une valeur. En fait, c’est le cas quand la journée est coupée en formation, vacances, travail etc… . Cela n’est pas le problème.

Par contre, j’ai une autre colonne avec une valeur importante dont j'ai besoin. Si je n’ai qu’une ligne par jour, c’est parfait car je n'ai dans cette colonne qu'un champ et donc un seule valeur.

Le problème est que quand ma journée est divisée, j’ai dans cette autre colonne autant de fois la même valeur qui est reprise. Or, je ne devrais, dans cette colonne n’avoir qu’une seule fois cette valeur.

Si, je fais supprimer les doublons…, je supprimerais toutes les lignes pour n’en garder qu’une. Cela n’est pas bon. Car je perdrais le détail de ma journée.

Il faudrait que je puisse non pas supprimer les lignes mais effacer les valeurs en doublons dans les cellules de cette colonne ou les mettre à 0.

Je ne sais pas si c’est possible. Vous comprenez qu’avec le nombre de ligne je ne peux pas passer en revue 900.000 lignes et effacer les valeurs manuellement.

Je joins un fichier avec deux personnes. J'ai mis en rouge dans un onglet les cas qui posent problèmes. Dans un autre onglet, ce à quoi je devrais arriver.

J’espère que vous saurez m’aider avec une formule ou une procédure.

D’avance merci.

Bonjour,

Oui c'est possible en utilisant VBA, je propose une solution dans ce fichier:

J'ai regardé si les dates qui se suivaient étaient identiques ou non, j'espère que ça marchera bien sur le vrai fichier également.

Bonsoir à tous,

Une proposition via Power Query (nativement intégré à votre version Excel) :

Note : La colonne "Diff" est calculée dans Power Query. Je n'ai donc pas téléchargé la source de cette colonne (tableau structuré initial redimensionné).

Bonjour,

@Ausecour, il me semble que cela fonctionne mais je suis occupé de tester sur les 800.000 lignes, mois par mois.

Je n'y connais pas grand chose mais je trouvais que quand j'ai lancé la macro, cela a été hyper rapide.

Est-ce que tu rends possible cet effacement pour le 1.000.000 de lignes du fichier ?

Est-ce que je pourrais avoir le détail de la macro ?

D'avance merci.

@JFL je n'ai pas pu tester car après 12 heures cela tournait encore ...

Maintenant, les données n'étaient pas dans l'ordre est ce que cela peut jouer ? Elles n'étaient pas rangées par dates qui se suivent.

Je viens encore de tester aussi sur power query.

J’ai du inteerompre

Bonjour à tous,

Proposition amendée !

Bonjour JFL,

J'ai testé cela va beaucoup plus vite mais j'ai remarqué un problème.

Dans les 800000 lignes qui correspondent à plusieurs centaines de personnes, j'ai des personnes qui ont certes un matricule différent mais qui ont des homonymes.

Et là cela perturbe la procédure parce qu'on dirait qu'un moment des mêmes noms viennent s'intégrer dans les jours. Et cela retire des valeurs à des jours qui n'ont pas deux cellules normalement.

Est-ce que tu vois ce que je veux dire ? Sinon, pour éviter cela je peux refaire le fichier sans les noms ?

Merci pour ton aide.

Bonjour de nouveau,

Une version amendée....

Attention ! Dans votre fichier test, un même matricule peut avoir DEUX noms différents...

Non, un matricule = 1 nom. Mais dans le fichier final que je récupère de power query les tris se font on dirait à un moment sur les noms

Je mets bien mon fichier trié par matricule et jour mais je le retrouve par jour mais avec des homonymes intercallés.

Je vais ressayer avec la version 20.

Bonsoir à tous,

tooniesgamer a écrit :

Non, un matricule = 1 nom.

Et pourtant :

en effet, je me rends compte de cela. C’est au moment de copier une partie du fichier j’ai tirer vers le bas.

C’est une erreur de retranscription.

Désolé.

Donc, je confirme que dans mon fichier réel, 1 matricule = 1 nom.

Par contre, nous avons plusieurs Dupont. Et chaque Dupont à 1 matricule différent.

Bonsoir de nouveau,

tooniesgamer a écrit :

en effet, je me rends compte de cela. C’est au moment de copier une partie du fichier j’ai tirer vers le bas.

C’est une erreur de retranscription.

Désolé.

Donc, je confirme que dans mon fichier réel, 1 matricule = 1 nom.

Par contre, nous avons plusieurs Dupont. Et chaque Dupont à 1 matricule différent.

Je désirais simplement attirer votre attention sur ce point.

En passant par le matricule (version 20), je pense que votre souci sera résolu.

Je suis curieux de votre retour sur la vélocité de la requête compte tenu d'une volumétrie.... conséquente !

Rebonsoir,

Non il n'y a pas de problème, je ne le prenais pas mal. Je trouve vraiment super que vous m'aidiez et c'est à moi de ne pas dire n'importe quoi.

J'ai testé, et je tombe sur le même résultat que la version VBA. J'ai regardé plus en avant et là ou il y a des différences, de ce que je vois le nouvel affichage est correct et efface les doublons. C'est donc très bien pour moi.

Le temps est de 3 minutes. C'est très bien vu le nombre de lignes.

Je voudrais mettre un peu d'ordre.

* est-ce que je peux retirer l'onglet situation idéale ?

* est-ce que je peux retirer J, K et L ?

Il faut aussi savoir que je n'ai pas mis toutes les colonnes dans le fichier. Est-ce que je peux les intégrer dans power query ?

Bonsoir de nouveau,

Trois minutes ?! Pour 900.000 lignes...c'est parfait !

1/ La feuille "situation idéale" peut être supprimée.

2/ Les colonnes K et L peuvent être supprimées mais pas la J (Power Query en a besoin pour calculer l'écart avec TSD (colonne I)

3/ D'autres colonnes peuvent être ajoutées à votre tableau structuré initial ("Tableau1").
Contraintes :
3-1 Ne pas modifier le nom des colonnes "importantes" ( Matr., Date, TSD, Done)
3-2 Il faudra revoir l'étape "tbl_dével" pour cocher toutes les case SAUF "Date" et "Matri."

tooniesgamer a écrit :

Bonjour,

@Ausecour, il me semble que cela fonctionne mais je suis occupé de tester sur les 800.000 lignes, mois par mois.

Je n'y connais pas grand chose mais je trouvais que quand j'ai lancé la macro, cela a été hyper rapide.

Est-ce que tu rends possible cet effacement pour le 1.000.000 de lignes du fichier ?

Est-ce que je pourrais avoir le détail de la macro ?

D'avance merci.

Bonjour,

Le code fonctionne bien entendu pour 1 000 000 de lignes.

Je peux expliquer rapidement le code, mais sans rentrer trop dans le détail (le but n'est pas non plus d'enseigner VBA depuis zéro):

'optimisations
Application.ScreenUpdating = False
methodeCalcul = Application.Calculation
Application.Calculation = xlCalculationManual

Je désactive la mise à jour automatique de l'affichage, et je mets la méthode de calcul du classeur à manuel en enregistrant le paramètre actuel dans une variable, pour le remettre après l'exécution, ça permet d'éviter de perdre du temps à faire des calculs pour afficher ou mettre à jours des données.

'initialisations
ligFin = Range("A" & Rows.Count).End(xlUp).Row
tableauDate = Range("C2", "C" & ligFin).Value
tableauTSD = Range("I2", "I" & ligFin).Value

J'utilise une astuce pour récupérer la dernière ligne contenant une donnée, avec le range.end(xlup).row, tu peux voir ce que fait chaque fonction dans les docs de microsoft, mais concrètement je pars de la ligne à la dernière ligne du classeur, dans la colonne A, je remonte jusqu'à trouver une cellule avec une info dedans, et je prends son numéro de ligne, comme ça je n'ai pas besoin de savoir le nombre de lignes dans ton tableau en avance.

J'utilise ce numéro de ligne pour enregistrer dans des tableaux VBA les données en colonne C et I, passer par ce type de tableau permet une exécution plus rapide.

'recherche de doublons
For lig = LBound(tableauDate, 1) + 1 To UBound(tableauDate, 1)
If tableauDate(lig, 1) = tableauDate(lig - 1, 1) Then
tableauTSD(lig, 1) = 0
End If
Next lig

Je parcours toutes les lignes du tableau en commençant par la deuxième ligne de données à l'aide d'une boucle, à chaque itération je regarde si la date de la ligne actuelle est la même ou non que la date de la ligne précédente (c'est comme ça que j'identifie les doublons), et si c'est le cas, je modifie la valeur du TSD en lui affectant 0, je commence par la deuxième ligne du tableau car la première n'a pas de ligne pour la comparaison.

'export de la modification
Range("I2", "I" & ligFin).Value = tableauTSD

Une fois les valeurs du TSD mises à jour, j'exporte le résultat dans la feuille.

'retour à la normale de l'application
Application.ScreenUpdating = True
Application.Calculation = methodeCalcul
Application.Calculate

Comme le programme est fini, je rends la main à l'affichage et remet le paramètre de calcul que le fichier avait avant l'exécution du code, puis je lance un petit calcul histoire de tout mettre à jour si besoin.

@ausecour Merci pour le détail. Je crois que cela est un peu trop compliqué pour moi. Je vais essayer de reproduire.

@JPL Merci également pour ton aide efficace. Comment pourrais-je avoir la procédure.

Je pense que Power Query me serait plus accessible. J'ai déjà utilisé cela suite à une demande ici. Mais je ne vois pas bien les actions que tu prends.

Pourrais tu me donner quelques détails en partant du fichier de base, cela m'évitera de jouer au singe savant en reproduisant bêtement.

En tout cas je vous remercie pour votre aide précieuse.

Merci.

Toonies

Bonjour à tous,

Pour prendre connaissance des différentes étapes appliquées au tableau structuré initial ("Tableau1"), faire un clic droit sur le tableau retourné par Power Query, "Table / Modifier la requête". Cela vous ouvrira l'éditeur de Power Query. Les différentes étapes sont portées à droite de cette fenêtre. Et l'intégralité du code (langage M) se trouve dans le menu "Editeur avancé".

Si une étape vous pose souci, revenez vers nous !

Bon apprentissage.

Génial ! Je regarde cela.

Petite question : ou puis je trouver le commandes ou opération de power query ?

Bonjour à tous,

tooniesgamer a écrit :

Petite question : ou puis je trouver le commandes ou opération de power query ?

Pourriez-vous expliciter votre demande ?