XL 2019 Duplication P fois des lignes ayant le dernier chiffre non vide le plus élevé

gthe · 18 Novembre 2020

Bonjour !
Je souhaite quelque chose d'assez simple (mais je ne sais pas utiliser VBA ).

Soit un tableau de la taille de la feuille Excel entière.
Ce tableau peut contenir des cases vides, mais la première colonne est toujours remplie (si ça peut aider).

Je souhaiterais dupliquer toutes les lignes P fois possédant leur dernière case non vide la plus élevée parmi toutes les valeurs du tableau.

Par exemple :

1 2 3 1 2 3
1 2 1 1 2
1 3 2 1 2 2
1 2 3 1 2 1
1 3 2 1 2 1 2
3 1 2 2 1 3
2 1 3 1 2 3
2 2 1 1 3
2 1 2 3 1 1
1 2 1 1 2 1 3 1 2
2 1 2 1 2 3 3
1 2 2 3 1 1 2

où seules les lignes suivantes seraient dupliquées P fois :

1 2 3 1 2 3
3 1 2 2 1 3
2 1 3 1 2 3
2 2 1 1 3
2 1 2 1 2 3 3

Nota : je peux être susceptible d'être confronté à des tableaux d'un million de lignes, ce serait un gros plus si le programme s'exécute "assez" rapidement.

En vous remerciant infinement,

G.

mapomme · 18 Novembre 2020

Bonsoir, et bienvenue sur XLD

gthe à dit:
susceptible d'être confronté à des tableaux d'un million de lignes

Si avec la duplication on atteint et dépasse le nombre max de ligne d'une feuille, que fait-on ?

Dans votre exemple vous parlez de p=3. p est-il fixe ? Dans ce cas comment le calcule-t-on ? Peut-il varier suivant les lignes ?

Indiquez dans votre profil votre version d'Excel svp

gthe · 18 Novembre 2020

Bonjour
Je suis sur Excel Office 2019, j'irai faire la modification.

Oui, l'idée est juste dupliquer P fois chacune des lignes possédant leur dernière case non vide la plus élevée.
Par contre, il faut que je puisse modifier P à ma guise.
P est un nombre que je connais. P peut varier de 1 à 1 000 000. (mais on sera très vite limité si on met 1 000 000 bien sûr).

Sinon, je connais la formule qui permet de connaître le nombre de cases concernées, ce qui fait que j'arriverai à m'en sortir pour rentrer des valeurs ne me faisant pas dépasser les 1 048 576 lignes. Mais c'est vraiment une excellente remarque !

S'il y a besoin d'autres précisions, ne surtout pas hésiter

En tout cas merci

mapomme · 18 Novembre 2020

gthe à dit:
S'il y a besoin d'autres précisions, ne surtout pas hésiter

Pas tout compris.
Dans votre exemples les lignes se terminent toutes par 1, 2 ou 3.
Peut-on avoir des lignes se terminant par 4 avec 4 le maximum de la ligne et dans ce cas duplique-t-on la ligne 4 fois ?
Puis la ligne suivante qui pourrait se terminer par 6 (max de ligne) et on dupliquerait six fois la ligne ?

gthe · 18 Novembre 2020

Bien sûr, je réponds tout de suite

J'ai fait un exemple volontairement simple où le max était 3. Mais dans le fichier que je vais traiter, on peut atteindre les 10 voire les 20 (je ne pense pas que je dépasserai 20).

Le nombre de fois qu'on duplique la ligne n'a aucun lien avec les données affichées. C'est l'utilisateur qui prend sa décision et fixe ce nombre P tel qu'il veut. P peut valoir jusqu'à 1 000 000 (mais il ne pourra dupliquer qu'une ligne dans ce cas là car je crois que la limite d'Excel est à égale à une puissance de 2 juste supérieure à 1 000 000).

Par contre, l'utilisateur doit avoir la liberté de choisir P avant d'exécuter le programme (P étant le nombre de fois qu'on duplique les lignes concernées, c'est à dire toutes celles qui se terminent par le nombre maximum).

mapomme · 18 Novembre 2020

Re,

C'est plus clair mais ce soir au lit...

gthe · 18 Novembre 2020

Re,
Aucun problème, je serai toujours là demain (sauf la journée où je serai en télétravail)

Au lit aussi pour moi...

mapomme · 19 Novembre 2020

Bonjour @gthe

Après réflexion, j'ai choisi une méthode en passant par un fichier Excel externe de type .CSV nommé XXXXX.csv (séparateur= point-virgule). Ce fichier est dans le répertoire du fichier de la macro.

Ceci permet de stocker quelque part toutes les lignes issues du traitement même si on dépasse la limite Excel du nombre de ligne d'une feuille.

Il suffit de double-cliquer sur ce fichier Excel. Excel normalement va ouvrir correctement le fichier résultat.

Par rapport à votre exemple, je duplique la ligne n° 4 (contrairement à vous). Cette ligne se termine par 2 et 2 est la bien le max de ligne n°4. Pour ce que j'en ai compris, p n'a rien à voir avec le fait que la ligne soit à dupliquer ou non.

le bouton "Initialiser" permet de remplir les lignes sources
le bouton "Dupliquer" lance la macro de duplication
la macro demande d'abord le nombre de fois p à dupliquer
si p = 0, on retrouve les données initiales sans modification
si le nombre de lignes est supérieur au nombre max de lignes d'une feuille, alors on avertit l'utilisateur
le fichier XXXXX.csv comporte toujours toutes les lignes du résultat

Le code est dans module1. Il y a trois constantes en début de code à modifier le cas échéant :

Const maFeuille = "Feuil1" ' nom de la feuille source
Const Premlig = 3 ' numéro de la première ligne des données
Const Pas = 50000 ' le pas de lecture des données

Sur mon PC, pour 500 000 initialisées:

avec p = 2, le traitement dure 2,8 s pour 1 000 008 lignes de résultat
avec p = 3, le traitement dure 3,0 s pour 1 250 010 lignes de résultat

job75 · 19 Novembre 2020

Bonjour gthe, mapomme,

Si l'on préfère tout faire sur le fichier Excel voyez le fichier joint et cette macro :

VB:

Sub Resultat()
If [C1] < 0 Then Exit Sub
Dim t#, P&, dest As Range, rc&, tablo, maxi#, ncol%, resu(), i&, j%, dercol%, n&, k&
t = Timer
P = [C1] 'nombre de copies
Set dest = Sheets("Résultat").[A1] '1ère cellule des résultats, à adapter
rc = Rows.Count - dest.Row + 1
tablo = ActiveSheet.UsedRange.Offset(1) 'matrice, plus rapide
maxi = Application.Max(tablo)
ncol = UBound(tablo, 2)
ReDim resu(1 To rc, 1 To ncol)
For i = 1 To UBound(tablo)
    For j = ncol To 1 Step -1
        If tablo(i, j) <> "" Then Exit For
    Next j
    dercol = j
    If dercol Then
        n = n + 1
        If n > rc Then GoTo 1
        For j = 1 To dercol
            resu(n, j) = tablo(i, j)
        Next
        If tablo(i, dercol) = maxi Then
            For k = 1 To P
                n = n + 1
                If n > rc Then GoTo 1
                For j = 1 To dercol
                    resu(n, j) = tablo(i, j)
            Next j, k
        End If
    End If
Next i
'---restitution---
With dest
    If n Then .Resize(n, ncol) = resu
    .Offset(n).Resize(rc - n - .Row + 1, ncol).ClearContents 'RAZ en dessous
    .Offset(, ncol).EntireColumn.Resize(, Columns.Count - ncol - .Column + 1).ClearContents 'RAZ à droite
    With .Parent.UsedRange: End With 'actualise les barres de défilement
    .Parent.Activate
End With
MsgBox "Feuille Résultat calculée en " & Format(Timer - t, "0.00 \sec")
Exit Sub
1 MsgBox "Tableau des résultats trop grand !", vbCritical
End Sub

Pour tester avec P = 3 j'ai recopié les lignes 3:14 sur 456 000 lignes (fichier de 16 Mo).

La macro s'exécute en 6 secondes chez moi pour donner 1 026 000 lignes (fichier de 42 Mo).

Edit : la durée d'exécution est pour 94% celle de la restitution sur la feuille.

En utilisant un fichier texte comme l'a fait mapomme c'est effectivement plus rapide.

A+

gthe · 19 Novembre 2020

Bonjour,
Merci beaucoup pour vos contributions, je viens juste de pouvoir regarder ça !
Je vous fais un retour dans l'heure si je peux

gthe · 20 Novembre 2020

Bonjour job75, mapomme,

Votre travail est hallucinant, c'est vraiment bien fait !
J'apprécie vraiment vos deux macro, merci beaucoup

@job75 , je n'ai rien à dire, c'est parfait ! Le temps de calcul est très satisfaisant. Je reviendrai vers vous si je rencontres des éventuels bugs ou si je n'arrivais pas à avoir le bon nombre de duplications ou les bonnes lignes dupliquées.
==> Je souhaitais juste vous demander si même des données écrites en AAA1000000 sont susceptibles d'être dupliquées ? En d'autres termes, quelle est la limite de la plage des données que je peux inscrire ? Un tableau de (A3:CZ175236) peut-il passer ?

@mapomme, votre travail est vraiment remarquable ! Les temps de calcul sont monstrueux. Quelques remarques cependant pour bien cerner la problématique :

Par rapport à la méthode du .CSV, je ne vois absolument aucun inconvénient.
par rapport au bouton "initialiser", je comprends que c'est pour réaliser une copie de ce qu'il y a dans l'encadré noir, de manière à générer un multiple de 12 lignes ? (et c'est un moyen plutôt rapide de vérifier la vitesse d'écriture ? C'est intelligent !)
Dans mon premier post, j'avais indiqué qu'il faut dupliquer P fois toutes les lignes possédant leur dernière case non vide la plus élevée parmi toutes les valeurs du tableau. Mais j'ai oublié de rappeler ce qu'il y avait en gras par la suite, donc mes excuses...
==> C'est la raison pour laquelle la combinaison "12112" n'était pas à retenir dans l'exemple que j'avais donné.
Du coup, la macro ne prend en considération que ce qu'il y a dans les limites du cadre noir ou de ses éventuelles recopies ? Par exemple, admettons que j'ai un tableau (A3:CZ175236) remplies de valeurs (certaines cases pouvant être vides un peu comme dans mon exemple, mais jamais une ligne entière), celui-ci pourrait-il être pris en compte par la macro ? J'avais écrit "soit un tableau Excel de la feuille entière" dans mon post de départ.

Voilà pour les quelques remarques ! Très honnêtement, ce n'ont l'air d'être que des questions de dimensionnement des données à prendre en compte. Dans l'esprit, à part la remarque en rouge qui peut être de nature à ne pas me fournir le résultat attendu, je pense qu'on y est presque !

En tout cas, votre travail à tous les deux est remarquable... Si vous avez des pistes pour que je me forme sur VBA je suis preneur Très clairement, vous donnez envie de s'y mettre

Merci encore à vous deux !

job75 · 20 Novembre 2020

gthe à dit:
==> Je souhaitais juste vous demander si même des données écrites en AAA1000000 sont susceptibles d'être dupliquées ? En d'autres termes, quelle est la limite de la plage des données que je peux inscrire ? Un tableau de (A3:CZ175236) peut-il passer ?

On peut remplir les cellules que l'on veut en Feuil1, A3:CZ175236 ne pose aucun problème.

Bonne nuit.

gthe · 20 Novembre 2020

@job75 , c'est très fort en tout cas (car beaucoup de programmes que j'ai vu traîner sur internet ne faisaient pas ça, et c'est une des raisons qui font que j'ai créée ce post !).
Merci en tout cas pour cette précision,
Bonne nuit
G.H

mapomme · 20 Novembre 2020

Re,

Concernant les données prises en compte, tout ce qui est sur la feuille sera utilisé.

Pour ne duplquer que les vles lignes dont la dernière valeur est égale au max de toutes les valeurs de la feuille, je vais le faire (la macro sera plus rapide). Je reviens le poster plus tard.

mapomme · 20 Novembre 2020

Re,
Je reviens avec la v2 adaptée

nota: plus il y a des lignes "longues", plus le temps d'exécution sera grand.

XL 2019 Duplication P fois des lignes ayant le dernier chiffre non vide le plus élevé

gthe

XLDnaute Junior

Pièces jointes

mapomme

XLDnaute Barbatruc

gthe

XLDnaute Junior

mapomme

XLDnaute Barbatruc

gthe

XLDnaute Junior

mapomme

XLDnaute Barbatruc

gthe

XLDnaute Junior

mapomme

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

gthe

XLDnaute Junior

gthe

XLDnaute Junior

job75

XLDnaute Barbatruc

gthe

XLDnaute Junior

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

Discussions similaires