Dupliquer des cellules sur plusieurs lignes

[virginie_kirika]

Bonjour,
après une recherche infructueuse sur le net (ne sachant comment formuler la problématique) je vous soumets ce problème.

Voici l'idée :
j'ai un onglet de plusieurs centaines de lignes mais avec 4 colonnes :
"Prénom" / "Nom" / "Couleur 1" / "Couleur 2".
Sachant que les couleurs peuvent parfois être vide, ce n'est pas un problème.

Exemple :
Ligne 1 : Martin / GRISET / Noir / Rouge
Ligne 2 : Vanessa / ROBAD / Vert / Noir
Ligne 3 : Clara / DELMO / Noir / (vide)
Ligne 4 : Lola / ELFA / (vide) / (vide)

Je voudrais copier cet onglet mais en faisant en sorte de n'avoir que 3 colonnes :
"Prénom" / "Nom" / "Couleur",
ce qui signifie que je me retrouve systématiquement avec 2 lignes ayant le même Nom et Prénom, même si certaines couleurs seront vides.

Dans l'exemple cela deviendrait :
Ligne 1 : Martin / GRISET / Noir
Ligne 2 : Martin / GRISET / Rouge
Ligne 3 : Vanessa / ROBAD / Vert
Ligne 4 : Vanessa / ROBAD / Noir
Ligne 5 : Clara / DELMO / Noir
Ligne 6 : Clara / DELMO / (vide)
Ligne 7 : Lola / ELFA / (vide)
Ligne 7 : Lola / ELFA / (vide)

D'avance merci pour vos retours, je ne vois pas de solutions alors que j'aurai des centaines de réponses !

 

[Jacky67]

Bonjour,
après une recherche infructueuse sur le net (ne sachant comment formuler la problématique) je vous soumets ce problème.

Voici l'idée :
j'ai un onglet de plusieurs centaines de lignes mais avec 4 colonnes :
"Prénom" / "Nom" / "Couleur 1" / "Couleur 2".
Sachant que les couleurs peuvent parfois être vide, ce n'est pas un problème.

Exemple :
Ligne 1 : Martin / GRISET / Noir / Rouge
Ligne 2 : Vanessa / ROBAD / Vert / Noir
Ligne 3 : Clara / DELMO / Noir / (vide)
Ligne 4 : Lola / ELFA / (vide) / (vide)

Je voudrais copier cet onglet mais en faisant en sorte de n'avoir que 3 colonnes :
"Prénom" / "Nom" / "Couleur",
ce qui signifie que je me retrouve systématiquement avec 2 lignes ayant le même Nom et Prénom, même si certaines couleurs seront vides.

Dans l'exemple cela deviendrait :
Ligne 1 : Martin / GRISET / Noir
Ligne 2 : Martin / GRISET / Rouge
Ligne 3 : Vanessa / ROBAD / Vert
Ligne 4 : Vanessa / ROBAD / Noir
Ligne 5 : Clara / DELMO / Noir
Ligne 6 : Clara / DELMO / (vide)
Ligne 7 : Lola / ELFA / (vide)
Ligne 7 : Lola / ELFA / (vide)

D'avance merci pour vos retours, je ne vois pas de solutions alors que j'aurai des centaines de réponses !

Bonsoir,
Avec Feuil1 les données source
En feuil2 le résultat
Exemple avec un peu de VBA en PJ

 

[job75]

Bonjour virginie_kirika, bienvenue sur XLD, salut Jacky67,

En A2 de Feuil2, à tirer sur B2 et vers le bas :

=T(DECALER(Feuil1!A$1;LIGNE()/2;))

En C2 de Feuil2, à tirer vers le bas :

=T(DECALER(Feuil1!C$1;LIGNE()/2;MOD(LIGNE();2)))

Fichier joint.

A+

 

[job75]

Re,

Une solution simple et très rapide en VBA :

Private Sub Worksheet_Activate()
If FilterMode Then ShowAllData 'si la feuille est filtrée
With Feuil1.[A1].CurrentRegion 'Feuil1 est le CodeName de la feuille source
  With [A2].Resize(2 * .Rows.Count, 3)
    .Formula = "=T(OFFSET(Feuil1!A$1,ROW()/2,(COLUMN()=3)*MOD(ROW(),2)))"
    .Value = .Value 'supprime les formules
  End With
  Range("A" & 2 * .Rows.Count & ":C" & Rows.Count).Delete xlUp 'RAZ sous le tableau
End With
End Sub

Cette macro est à placer dans le code de Feuil2 et s'exécute quand on active la feuille.

Une seule formule est entrée par la macro dans toute la plage :

=T(DECALER(Feuil1!A$1;LIGNE()/2;(COLONNE()=3)*MOD(LIGNE();2)))

Edit : j'ai ajouté une ligne s'il prenait fantaisie de filtrer Feuil2.

Fichier .xlsm joint.

A+

 

[job75]

Re,

Sur Win 10 - Excel 2013, avec 40 000 lignes en Feuil1, la macro s'exécute en 1,5 seconde.

A+

 

[Jacky67]

Hello Job75
Jolie...C'est autre chose que mes 14 secondes pour 40 000 lignes
Est-il correcte de dire que dans ce cas, le T n'est là que pour éviter les zéros ?

 

[job75]

Re,

Est-il correcte de dire que dans ce cas, le T n'est là que pour éviter les zéros ?

Oui tout à fait.

Et je viens d'ajouter une ligne de code pour le cas où Feuil2 serait filtrée.

A+

 

[Jacky67]

Merci Job75, c'est dans la boite à idées
Le "ShowAllData", bien utile, je l'oublie souvent aussi 🙁

Bonne nuit

 

[virginie_kirika]

Wow,
et bien merci à tous de vos contributions, je ne m'attendais pas à autant de solutions rapides et efficaces !
Merci encore et bonne journée à tous.

 

[job75]

Bonjour virginie_kirika, Jacky67, le forum,

Avec des tableaux VBA c'est encore plus rapide :

Private Sub Worksheet_Activate()
Dim t1, t2$(), i&, j&
t1 = Feuil1.[A1].CurrentRegion.Resize(, 4) 'Feuil1 est le CodeName de la feuille source
ReDim t2(1 To 2 * UBound(t1) - 1, 1 To 3)
For i = 1 To UBound(t2)
  j = 1 + i \ 2
  t2(i, 1) = t1(j, 1): t2(i, 2) = t1(j, 2): t2(i, 3) = t1(j, 3 + i Mod 2)
Next
t2(1, 3) = "Couleur"
If FilterMode Then ShowAllData 'si la feuille est filtrée
[A1].Resize(i - 1, 3) = t2 'restitution
Range("A" & i & ":C" & Rows.Count).ClearContents 'RAZ sous le tableau
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Fichier (2), avec 40 000 lignes en Feuil1 => 0,47 seconde chez moi.

PS : i \ 2 c'est la même chose que Int(i / 2).

Bonne journée.