Microsoft 365 meilleure solution (?) pour coller plusieurs colonnes dans une seule

[Eric 888]

Bonjour

J'utilise la méthode reproduite dans le fichier en PJ pour récupérer dans une seule colonne les cellules non vides de plusieurs colonnes remplissant une condition (fonctions Excel récentes non utilisées pour compatibilité descendante)

Cela marche très bien, mais je dois l'appliquer à un tableur comportant 200 colonnes de 5000 lignes, soit un million de valeurs en matrice à traiter... donc ça rame grave (la condition est dynamique), et j'arrive presque à la limite de lignes Excel donc je suis bloqué si je veux ajouter des colonnes.

(Le fichier en PJ comporte qq "astuces" basées sur le fait que je veux récupérer des données numériques, mais il est facilement adaptables à des valeurs autres.)

Je suis plutôt nul en VBA et toutes mes tentatives pour adapter des fonctions trouvées ici et là ont échoué. Je serais très preneur ;-)

Idem si qq'un sait faire la même chose avec des formules plus économes / plus simples.

Et si un autre débutant trouve ma solution exploitable, welcome: j'ai vu que nous étions nombreux à affronter ce besoin.

Merci d'avance
Eric

 

[sylvanu]

Bonjour Eric,
Un essai en PJ avec :

Sub Liste()
Application.ScreenUpdating = False
Sheets("Résultat").Range("A:A").ClearContents                     ' effacement tableau sortie
T = [A1].CurrentRegion                  ' tranfert tableau dans array
Lmax = UBound(T): Cmax = UBound(T, 2)   ' calcul ligne colonne maxi
ReDim Tout(Lmax * Cmax)                 ' ouvre array de sortie avec taille LmaxCmax
Tout(0) = "Résultat"                    ' init titre
IndW = 1                                ' init index d'écriture
For C = 1 To Cmax                       ' pour toute colonne
    For L = 1 To Lmax                   ' pour toute ligne
        If T(L, C) <> "" Then           ' si non vide ( le double if permet d'accélérer )
            If T(L, C) <> 0 And IsNumeric(T(L, C)) = True Then  ' si non nul et numérique
                Tout(IndW) = T(L, C)    ' on range
                IndW = IndW + 1         ' on incrémente l'index d'écriture
            End If
        End If
    Next L
Next C
Sheets("Résultat").Range("A1").Resize(IndW, 1).Value = Application.Transpose(Tout) ' on tranfère l'array dans la feuille.
End Sub

Le tableau de sortie est en feuille 2.
En terme de vitesse entre les deux solutions, aucune idée. A vous de tester sur beaucoup de lignes et colonnes.

 

[Eric 888]

Bonjour Sylvanu

Merci pour la rapidité
Je m'aperçois que mon exemple a créé une confusion.
Je ne cherche pas à éliminer les valeurs non numériques
Je cherche à éliminer les colonnes dont l'en-tête est NON (j'y ai mis des valeurs 'alerte' pour faciliter la détection d'erreurs si le résultat n'était pas le bon.

donc la logique serait
calculer le nombre de colonnes non vides
on part de la 1re colonne
si la cellule 1re ligne = OUI, alors copier colonne (sans la 1re ligne)
si NON, passer à colonne suivante
on fait comme ça autant de fois qu'il y a de colonnes non vides
à la fin, on colle les données copiées (donc les données figurant dans les colonnes avec en-tête 'OUI")

je ne sais pas si je suis clair, je ne suis pas programmeur je n'arrive pas à coder ça à partir de votre exemple

merci d'avance ;-)

 

[sylvanu]

Re Eric,
Oups!
J'ai le droit à un second essai ?

Sub Liste()
Application.ScreenUpdating = False
Sheets("Résultat").Range("A:A").ClearContents                     ' effacement tableau sortie
T = [A1].CurrentRegion                  ' tranfert tableau dans array
Lmax = UBound(T): Cmax = UBound(T, 2)   ' calcul ligne colonne maxi
ReDim Tout(Lmax * Cmax)                 ' ouvre array de sortie avec taille LmaxCmax
Tout(0) = "OUI"                         ' init titre
IndW = 1                                ' init index d'écriture
For C = 1 To Cmax                       ' pour toute colonne
    If T(1, C) = "oui" Then             ' si entete colonne = oui on copie la colonne
        For L = 2 To Lmax               ' pour toute ligne
            If T(L, C) <> "" Then       ' si non vide
                Tout(IndW) = T(L, C)    ' on range
                IndW = IndW + 1         ' on incrémente l'index d'écriture
            End If
        Next L
    End If
Next C
Sheets("Résultat").Range("A1").Resize(IndW, 1).Value = Application.Transpose(Tout) ' on tranfère l'array dans la feuille.
End Sub

 

[Eric 888]

Hello hello

j'ai pu l'adapter à mon fichier, ça marche à la perfection, et c'est robuste qq soit l'évolution de mes données, et ça va hyper vite sans alourdir le fichier (pas comme ma solution avec plusieurs millions de cellules et de calculs... )

il y a juste un truc que je n'arrive pas à modifier, mais c'est un détail:
je n'ai pas besoin du titre "OUI" en cellule A1 de la feuille résultats (en fait je dois ne pas l'avoir)
j'ai désactivé la ligne Tout(0)="OUI", ça me génère une cellule A1 vide dont je n'arrive pas à me débarrasser ; en soi ce n'est pas du tout un souci pour mon usage, si la cellule est vide c'est OK, mais je suis du genre maniaque (et puis j'aime bien comprendre donc je bidouille )

prochaine étape:
compléter la macro pour qu'elle crée un fichier texte avec les valeurs de la feuille résultat dans le dossier où se trouve mon tableur (ou plutôt un sous-dossier) - j'ai trouvé des bouts de code ici et là, y en a bien un que j'arriverai à exploiter...
jusqu'ici, faire un copier-coller manuel dans mon fichier texte était un détail comparé au temps de calcul, mais là ce serait dommage de ne pas aller au bout du truc

en tout cas, un grand merci: solution parfaite, livraison ultra-rapide, et même SAV
c'est un truc qui m'a toujours épaté avec les forums experts: bon esprit, générosité, sens de l'entraide... et bonnes manières - quand tu compares avec les réseaux sociaux...

++
Eric

 

[sylvanu]

Re,
Il suffit de supprimer Tout(0)="OUI" et initialiser IndW à 0.
( les cellules commencent en 1 mais l'array commence en 0 )

 

[Eric 888]

Re,
Il suffit de supprimer Tout(0)="OUI" et initialiser IndW à 0.
( les cellules commencent en 1 mais l'array commence en 0 )

thx

 

[Eric 888]

help Sylvanu

j'ai crié victoire trop vite
en PJ mon "vrai" fichier, les données sont dans la feuille récup GPX et le résultat dans la feuille GPX
(certaines données sont tirées d'un autre fichier mais a priori cela doit fonctionner sans mise à jour de ces données, et il n'y a aucune donnée personnelle au sens RGPD)

le principe:
dans la feuille récup GPX, un menu cellule C12 (cellule en jaune) permet de choisir des colonnes (cela actualise les valeurs oui/non de la ligne supérieure), il ne reste plus qu'à exécuter ta macro et ça marche impecc...

... sauf quand je choisis "tous" dans mon menu, ce qui revient à mettre à oui toutes les colonnes où il y a des données; quand je fais ça, ça marche jusqu'à la ligne 17790, et ensuite je récupère #N/A jusqu'à la fin

comme je ne comprenais pas ce qui se passait (ça ne semble pas faire tant de données que ça pour un Array), j'ai forcé la valeur Lmax à 5000 pour voir, et ça m'a retourné plus de valeurs mais toujours pas toutes les valeurs

j'ai un peu joué avec ce paramètre mais je n'ai pas la compétence pour analyser les résultats; j'ai néanmoins l'impression que ça vient du calcul de Lmax, mais je ne comprends pas pourquoi

si tu pouvais jeter un oeil?

merci d'avance
Eric

 

[sylvanu]

Bonjour Eric,
Pour une raison que j'ignore le T = [C1].CurrentRegion donne un Lmax de 20005 et Cmax de 204.
Le 20005 est faux j'ai vérifié il n'y a rien des lignes 2500 à 65000.
Et comme je dimensionne mon tableau de sortie avec Lmax Cmax qui est le maximum possible j'obtiens plus de 4 000 000, ce qui est supérieur au nombre de lignes possibles dans XL.
J'ai donc changé de stratégie. Pour chaque colonne ( que j'ai limité à 1000 ) je compte le nombre d'éléments et je dimensionne les tableaux d'entrée et de sortie en conséquence.
En PJ un essai. ( Attention j'ai mis "oui" dans toutes les colonnes por test. A modifier )
J'ai crée la variable PremièreColonne que j'ai initialisé à 4 ( colonne D ). A modifier s'il faut prendre en compte la colonne C.
J'ai mis un message à la fin qui donne quelques infos pour la mise au point. A supprimer si tout est ok.

Lien : https://www.cjoint.com/c/KEpgWGFyLLY

 

[Eric 888]

hello

cela fonctionne désormais parfaitement, merci pour cette réponse efficace et rapide

pour ta solution précédente:
le 20005 n'est pas faux, cela correspond à la colonne A cachée utilisée pour mise en forme conditionnelle
j'ai moi aussi pensé que ça pouvait venir de là, c'est pour ça que

• j'ai remplacé [A1].CurrentRegion par [C1].CurrentRegion => pas d'amélioration
• j'ai supprimé la colonne A => pas d'amélioration
• j'ai copié les données pertinentes de mon tableur et les ai collées dans ton fichier solution => pas d'amélioration

j'ai fait divers tests, en sélectionnant des colonnes (en-tête "oui") selon diverses séquences, cela plante systématiquement à la ligne 27956 de la feuille de sortie; et ce n'est pas une colonne en particulier qui met le bazar, on dirait plutôt que c'est le nombre de lignes cumulées des colonnes sélectionnées

bref, les mystères d'XL...

bon WE (pluvieux, chez moi )
++

 

[sylvanu]

Ceci explique cela.
CurrentRegion prends tout ce qu'il y a à droite et à gauche de la cellule sélectionnée jusqu'à ce qu'il trouve un vide. Ca, j'avais oublié.
Donc qu'on parte de A1 ou de C1 c'est pareil.
J'ai inséré une colonne entre A et B. Alors [C1].CurrentRegion donne bien 203 colonnes mais 2085 lignes car dans ce cas, comme B est vide, il ne prend pas A en considération.
Au moins il y a une explication rationnelle. Mais le principal reste que la dernière version marche.
Bon WE. ( chez moi aussi )