XL 2010 Plusieurs colonnes en une colonne

[CATPLUS]

Bonjour et Bonne Année 2018
Question?
Dans un tableau, plusieurs colonnes A C E (etc..) avec des noms "X, Y, Z ......", je souhaite regrouper ces noms dans une même colonne les uns en dessous des autres.
J'ai trouvé beaucoup de réponses, mais toujours très compliqué à mettre en oeuvre.
Y a-t-il une fonction simple (index, nbval, etc....)?
Merci de votre aide.

Cordialement

 

[Staple1600]

Bonjour le fil, le forum

@CATPLUS
Il y a une fonction simple pour nous aider à t'aider
Cliquer sur le bouton Téléverser un fichier pour joindre un fichier Excel
(qui nous permettra d'y voir plus clair et sera la base sur laquelle faire nos essais de formules et/ou macros

 

[CATPLUS]

Avec mes excuses
Dans la colonne S, fusionner les résultats de H, J, L, N, P et R
Best Regards

 

[Staple1600]

Re

Où en colonne S?
à partir de S2? ou S3? etc...
Par formules ou par macro?

 

[CATPLUS]

Merci Nicole
Cela correspond à ma demande
Juste une question? la même chose mais sans tri

Pour JM
A partir de S3 par formule c'est plus simple
Cordialement

 

[Staple1600]

Re

@CATPLUS
Pour info
La fomule de N. BISSON implique une macro

Par conséquent, puisque j'ai mis le nez dans VBE, je publie
(même si cela n'a pas l'efficience de N.BISSON)

Sub Test()
Dim c As Range, z$, t
For Each c In Range("H5:H24,J5:J24,L5:L24,N5:N24,P5:P24,R5:R24")
If Len(c) > 2 Then
z = z & "²" & c
End If
Next
t = Split(Mid(z, 2, Len(z) - 1), "²")
[S3].Resize(UBound(t) + 1) = Application.Transpose(t)
End Sub

 

[CATPLUS]

Un grand MERCI

En vous souhaitant une bonne journée.
Cordialement

 

[job75]

Bonjour à tous,

Pas besoin de VBA, il s'agit juste de décaler des plages.

Formule en S3 :

=SIERREUR(SI(LIGNE()<G$2+3;INDEX(H$5:H$24;LIGNE()-2);SI(LIGNE()<G$2+I$2+3;INDEX(J$5:J$24;LIGNE()-G$2-2);SI(LIGNE()<G$2+I$2+K$2+3;INDEX(L$5:L$24;LIGNE()-G$2-I$2-2);SI(LIGNE()<G$2+I$2+K$2+M$2+3;INDEX(N$5:N$24;LIGNE()-G$2-I$2-K$2-2);SI(LIGNE()<G$2+I$2+K$2+M$2+O$2+3;INDEX(P$5:P$24;LIGNE()-G$2-I$2-K$2-M$2-2);INDEX(R$5:R$24;LIGNE()-G$2-I$2-K$2-M$2-O$2-2))))));"")

Elle est un peu longuette mais une fois qu'on a compris le principe elle coule de source.

Fichier joint.

A+

 

[job75]

Re,

J'ai testé les durées du recalcul de la plage S3:S24.

Chez moi la fonction VBA prend 16 fois plus de temps (4,9 ms) que les formules Excel (0,3 ms).

A+

 

[job75]

Bonjour CATPLUS, le forum,

Deux remarques sur votre fichier :

- vous avez fait des validations matricielles sur beaucoup de vos formules, elles ne servent à rien

- il y a 2 doublons en colonne D, en lignes 217-218 et en lignes 381-384.

Bonne journée.

 

[job75]

Re,

Je préfère nettement cette formule en S5 :

=SI(LIGNES(S$5:S5)<=G$2;INDEX(H:H;LIGNE());SI(LIGNES(S$5:S5)<=G$2+I$2;INDEX(J:J;LIGNE()-G$2);SI(LIGNES(S$5:S5)<=G$2+I$2+K$2;INDEX(L:L;LIGNE()-G$2-I$2);SI(LIGNES(S$5:S5)<=G$2+I$2+K$2+M$2;INDEX(N:N;LIGNE()-G$2-I$2-K$2);SI(LIGNES(S$5:S5)<=G$2+I$2+K$2+M$2+O$2;INDEX(P:P;LIGNE()-G$2-I$2-K$2-M$2);INDEX(R:R;LIGNE()-G$2-I$2-K$2-M$2-O$2))))))

En effet il est plus logique de placer le tableau des résultats sur la même ligne que le tableau à gauche, et les plages étudiées sont illimitées.

On peut insérer des lignes ou colonnes sans avoir à modifier la formule.

Du fait des plages illimitées le calcul prend un peu plus de temps (0,6 ms).

Fichier (2).

A+

 

[job75]

Bonjour le forum,

En utilisant la plage auxiliaire F3: P3, formule beaucoup plus simple en S5 :

=INDEX(H:R;LIGNE()-RECHERCHE(LIGNE()-LIGNE(S$5);F$3:P$3);EQUIV(LIGNE()-LIGNE(S$5);F$3:P$3))

Elle est simple mais bien plus difficile à comprendre...

On évite la multiplication des imbrications : même si le nombre de colonnes à traiter est important elle fonctionnera sur Excel 2003 et versions antérieures où le nombre des imbrications est limité à 8.

La durée des calculs est inchangée (0,6 ms).

Fichier (3).

A+

 

[job75]

Bonjour le forum,

Fichier (3 bis) avec une autre utilisation de la plage auxiliaire et une formule encore plus simple :

=INDEX(H:R;LIGNE()+F$3-RECHERCHE(LIGNE();F$3:P$3);EQUIV(LIGNE();F$3:P$3))

Mais toujours pas facile à comprendre.

Bonne journée.

 

[job75]

Re,

On peut bien sûr utiliser le VBA.

Son seul intérêt est d'entrer la formule en colonne S dans la plage strictement nécessaire :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim h&
h = Application.Sum(Range([QTs], [QTs].End(xlToRight)))
If h < 1 Then h = 1 'sécurité
Application.EnableEvents = False 'désactive les évènements
[Deb].Resize(h) = [Deb].Formula 'recopie la formule en S5 vers le bas
[Deb].Offset(h).Resize(Rows.Count - h - [Deb].Row + 1).ClearContents 'RAZ en dessous
Application.EnableEvents = True 'réactive les évènements
End Sub

Fichier joint, voyez les cellules nommées QTs et Deb ainsi que la MFC en colonne S.

A+