XL 2016 Transposer toutes les trois lignes l'un en dessous de l'autre

[Loïc DUBOIS]

Bonjour à tous,

J'espère que vous allez bien ?

Je créé cette discussion aujourd'hui car j'ai un problème que je n'arrive pas à resoudre.

J'ai un fichier d'environ 65k lignes. Je voudrais prendre les trois première lignes puis les coller en transposer. Puis prendre les trois suivantes et les coller en dessous du collage précédent et ainsi de suite.

Je vous joins un fichier exemple. Feuille 1 : mon fichier de base. Feuille 2 : Ligne 1 à 3 coller en transposer et en dessous ligne 4 à 6 coller en transposer. Etant donné ma grande base de données, il faut que je puisse automatiser cela.

J'espère que vous serez en mesure de m'aider.

Si vous avez besoin de précision n'hésitez pas.

Merci d'avance,

Loïc DUBOIS

 

[Loïc DUBOIS]

Bonsoir à tous,

Non je n'ai pas d'explication.
Avec votre PJ qui a 375 lignes de 190 colonnes, cela génèrent une liste de 23750 lignes, et je dimensionne mon tableau de sortie à 23875.
Donc loin des limites. Normalement cela devrait marcher.
( à noter que je bosse avec 8Go de Ram, Win10 et XL2007 )

D'accord,
POur info j'ai environ 55 000 lignes donc par 3 cela fait 18333 annonces. Or j'ai 190 colonnes ce qui fait environ 3M5 lignes après le processus. J'avais donc pour ambition de faire 4 fichiers excel (1M * 3 + 500k). Peut-être qu'il faut redimensionner le tableau dans ces ordres de grandeurs ? (je suis désolé j'y connais pas grand chose en VBA...).

Merci d'avance pour votre réponse

Loïc

 

[soan]

Bonsoir sylvanu, le fil,

à noter que je bosse avec 8Go de Ram, Win10 et XL2007

ton verbe « bosser » est vraiment très inadéquat ! le verbe « bosser » est devenu interdit depuis bien longtemps ! tu dois dire : « à noter que j'm'amuse avec 8Go de Ram, Win10 et XL2007 » ; la preuve.​

soan

 

[sylvanu]

Bonsoir,
@Loïc, je ne répondait qu'à votre post #12. Je n'ai pas d'explication pourquoi cela ne marche pas. Car ma PJ marche très bien sur mon PC.
Le problème de taille fichier c'est autre chose.
@soan,
Je suis un vieux qui utilise u vieux langage ( Lien )

 

[soan]

@sylvanu

Je suis un vieux qui utilise un vieux langage

hélas, Fernand Raynaud aussi est vieux ! même que le pauvre a passé l'arme
à gauche : il est mort le 28 septembre 1973, à 47 ans seulement !

pourtant, je croyais que le rire c'est la santé ! (veuillez excuser ce léger cynisme ! )

soan

 

[Loïc DUBOIS]

Bonsoir à tous,

Non je n'ai pas d'explication.
Avec votre PJ qui a 375 lignes de 190 colonnes, cela génèrent une liste de 23750 lignes, et je dimensionne mon tableau de sortie à 23875.
Donc loin des limites. Normalement cela devrait marcher.
( à noter que je bosse avec 8Go de Ram, Win10 et XL2007 )

D'accord je comprends.

Pouvez-vous tester avec ce fichier voir si vous avez le même problème que moi (3k lignes, ce qui représente donc à la fin 1000/3 * 190 = 63270 lignes) ?

 

[sylvanu]

Bonsoir,
C'est dû à Indice qui est limité à 32768 avec le "as integer", remplacez Indice% par Indice.

Ceci dit, si le but est d'obtenir un fichier csv, pourquoi ne pas générer un fichier txt ( par ex avec la macro de Job ) puis de le renommer.
En PJ un essai il génère un fichier csv. Si votre outil accepte de le digérer alors c'est bon car les fichier txt n'ont pas de pb de longueur.
A la macro de Job j'ai fini avec :

' Fermeture fichier texte
Close #n
' Si le fichier csv existe on le supprime.
If ThisWorkbook.Path & Application.PathSeparator & "ESSAI.csv" <> "" And _
    Len(Dir(ThisWorkbook.Path & Application.PathSeparator & "ESSAI.csv")) > 0 Then
    Kill ThisWorkbook.Path & Application.PathSeparator & "ESSAI.csv"
End If
' On renomme le fichier txt en csv
Name ThisWorkbook.Path & Application.PathSeparator & "ESSAI.txt" As ThisWorkbook.Path & Application.PathSeparator & "ESSAI.csv"
MsgBox "Le fichier : " & ThisWorkbook.Path & Application.PathSeparator & "ESSAI.csv" & " a été généré."
End Sub

Le fichier csv généré est bien lisible pas XL par exemple.

 

[soan]

Bonjour Loïc, le fil,

* sur "Feuil2", y'a aucune donnée : la feuille est entièrement vide.

* va sur "Feuil1", et fais Ctrl e ➯ travail effectué. ​

(sur mon PC, ça prend moins d'une minute ! )

c'que j'ai écrit ci-dessus a un air de déjà vu, hein ? (comme pour mon post #2) ... oui, sauf que cette fois, l'affichage des résultats se fait sur de multiples triples-colonnes, et pas seulement sur la triple-colonne "A:C" ; la ligne maximum LigMax est la ligne 200 (c'est très facilement adaptable dans le code VBA).
​

---

voici plusieurs exemples de place prise par les colonnes de résultat :

avec LigMax = 200, la dernière triple-colonnes utilisée est "RE:RG" : colonnes n° 473 à 475.

avec LigMax = 300, la dernière triple-colonnes utilisée est "LE:LG" : colonnes n° 317 à 319.

avec LigMax = 400, la dernière triple-colonnes utilisée est "iC:iE" : colonnes n° 237 à 239.

avec LigMax = 500, la dernière triple-colonnes utilisée est "GG:Gi" : colonnes n° 189 à 191.

avec LigMax = 600, la dernière triple-colonnes utilisée est "FA:FC" : colonnes n° 157 à 159.

avec LigMax = 700, la dernière triple-colonnes utilisée est "EC:EE" : colonnes n° 133 à 135.

avec LigMax = 800, la dernière triple-colonnes utilisée est "DM: DO" : colonnes n° 117 à 119.

avec LigMax = 900, la dernière triple-colonnes utilisée est "DA: DC" : colonnes n° 105 à 107.

avec LigMax = 1000, la dernière triple-colonnes utilisée est "CO:CQ" : colonnes n° 93 à 95.

avec LigMax = 2000, la dernière triple-colonnes utilisée est "AS:AU" : colonnes n° 45 à 47.

avec LigMax = 3000, la dernière triple-colonnes utilisée est "AC:AE" : colonnes n° 29 à 31.

avec LigMax = 4000, la dernière triple-colonnes utilisée est "U:W" : colonnes n° 21 à 23.

avec LigMax = 5000, la dernière triple-colonnes utilisée est "Q:S" : colonnes n° 17 à 19.

avec LigMax = 6000, la dernière triple-colonnes utilisée est "M:O" : colonnes n° 13 à 15.

avec LigMax = 8000, la dernière triple-colonnes utilisée est "i:K" : colonnes n° 9 à 11.

avec LigMax = 12000, la dernière triple-colonnes utilisée est "E:G" : colonnes n° 5 à 7.

à toi de choisir le nombre de lignes maximum que tu veux pour tes colonnes ; et c'est très simple à faire : dans le code VBA, tu as seulement le nombre de cette ligne à modifier :

Const LigMax& = 200 '<--- jusqu'à cette Ligne Maximum (à adapter)

pour tous mes essais : ça prend moins d'une minute, quel que soit le nombre de lignes maximum choisi.

remarque : avec un fichier Excel .xlsm (ou .xlsx), la toute dernière colonne d'une feuille Excel est la colonne "XFD" ; son n° est : 16384 ➯ un maximum de 16384 colonnes peut être utilisé ; donc même avec LigMax = 200, tu as une très grosse marge puisque la dernière colonne utilisée est "RG", de n° 475.​

---

code VBA (24 lignes) :

Option Explicit

Sub Essai()
  If ActiveSheet.Name <> "Feuil1" Then Exit Sub
  If IsEmpty([A1]) Then Exit Sub
  Dim dcol%: dcol = Cells(1, Columns.Count).End(1).Column
  Dim n&: n = Cells(Rows.Count, 1).End(3).Row: If n < 3 Then Exit Sub
  Const LigMax& = 200 '<--- jusqu'à cette Ligne Maximum (à adapter)
  Dim T, i&, j%, lig&, col%: Application.ScreenUpdating = 0
  T = [A1].Resize(n, dcol): col = 1
  Worksheets("Feuil2").Select: Columns.ClearContents
  For i = 1 To n Step 3
    For j = 1 To dcol
      lig = lig + 1
      With Cells(lig, col)
        .Value = T(i, j)
        .Offset(, 1) = T(i + 1, j)
        .Offset(, 2) = T(i + 2, j)
      End With
      If lig = LigMax Then lig = 0: col = col + 4
    Next j
  Next i
End Sub

soan

 

[sylvanu]

Bonjour tout le monde,
@soan,
Ca ne change rien si le résultat fait plus d'un million de lignes, XL ne saura pas le gérer.
@loïc,
En PJ un fichier rectifié, j'avais oublié de mettre un entête dans le fichier csv avec :

Print #n, "Nom champ 1;Nom champ 2;Nom champ 3"  'Inserer entetes

( Les noms sont à modifier si nécessaire )
Il serait intéressant de voir si le "bidule" est capable de lire ce fichier.

 

[soan]

Bonjour sylvanu, le fil,

Ça ne change rien si le résultat fait plus d'un million de lignes, XL ne saura pas le gérer.

c'est exact ... mais justement, ce que j'ai réalisé fait que les résultats sont sur moins d'un million de lignes puisque c'est sur plusieurs colonnes ; si tu relis bien attentivement tous les exemples de mon post #22 :

1) LigMax, le nombre de lignes maximum varie entre 200 et 12 000 lignes ; ça ne veut pas dire que c'est limité à 12000 lignes ; on peut aussi mettre LigMax à 1048570 ; c'est Loïc qui choisira ce qu'il préfère.

2) selon la variation de LigMax du point 1), le nombre de colonnes varie de 475 à 7 ; comme il y a plus de 16 000 colonnes, y'a amplement de la marge !

soan​

 

[sylvanu]

Re,
Je faisait référence à la remarque de Loïc du post #5 :

Effectivement comme l'indique Job75, la limite en terme de ligne est atteinte.
Connaissez-vous un moyen s'il existe d'augmenter cette limite vers les 3 millions ?

Donc je suis parti du principe que dans la réalité sont fichier de sortie aura plus d'un million de lignes.
( sinon votre approche comme la mienne marcheraient )

 

[soan]

@sylvanu

c'est bien ce que je dis : pour éviter que les résultats soient sur plus d'un million de lignes, le fait de répartir ces résultats sur plusieurs colonnes résout ce problème ; exemple : le résultat sur une triple-colonnes tiendrait sur 3 millions de lignes ; sur 100 fois plus de colonnes, donc sur 300 triples-colonnes (= 900 colonnes), le résultat tiendra sur 3 millions de lignes / 300 triples-colonnes = 10000 lignes ; c'est largement moins que la limite de 1048576 ! ce que j'appelle triple colonne, c'est ceci : un résultat a 3 éléments, de ce genre : AMZN.OQ1 ; 02/07/2018 ; 1713,78 ; laissons Loïc essayer mon fichier ; si ça marche : ok ; sinon, tant pis !

par contre, si c'est la base initiale sur "Feuil1" qui est sur 3 millions de lignes, alors là oui, ça pose problème ! au paragraphe précédent, je parle des résultats sur "Feuil2".​

soan

 

[sylvanu]

Exact, Laissons Loïc tester tout ça, car c'est un peu ambigüe.
J'étais parti du principe que son application exigeait un format csv sur trois colonnes, d'où le souci.

malheureusement ce fichier a pour vocation d'aller dans une application, et cette dernière n'accepte que les formats excel ou csv.

 

[Loïc DUBOIS]

Soan, Sylvanu,

Vos deux solutions marchent parfaitement. Merci beaucoup pour le temps que vous m'avez accordé.

Pour vous répondre, effectivement je ne peux utiliser que les trois premières colonnes.

Je vous souhaite une très bonne journée,

Loïc

 

[soan]

Bonjour Loïc,

Vos deux solutions marchent parfaitement.

merci pour ton retour !

---

je ne peux utiliser que les trois premières colonnes.

ça, c'est plus embêtant !

soan

 

[Loïc DUBOIS]

Bonjour Loïc,



merci pour ton retour !

---

ça, c'est plus embêtant !

soan

Hum pas vraiment, puisque je peux créer 4 fichiers excel, les transformer en csv puis les fusionner en csv. C'était donc une solution viable également (même si j'avoue avoir utilisé la solution de Sylvanu).

En tout cas merci beaucoup.

Loïc