Transformer ligne en colonne

[infotun]

Salut,

je voudrais faire une transformation sur des mots qui sont aligné le long d'une ligne
expemple:
1 mot1,mot2
2 mot3,mot4,mot5,mot6,mot7..etc
3 mot20,mot21
.
.
...etc

transformer:

1 mot1
1 mot2
2 mot3
2 mot4
2 mot5
2 mot6
2 mot7
..
3 mot20
3 mot21
...etc

ci-dessous une pièce jointe du fichier excel comme exemple.

merci pour votre aide

 

[kjin]

Re : Transformer ligne en colonne

Bonsoir,

Sub zozo()
Dim i As Long, j As Long, c As Range
For Each c In Range("B2:B" & Range("A65000").End(xlUp).Row)
    t = Split(c, ",")
    If UBound(t) > 0 Then
        For i = LBound(t) To UBound(t)
            j = j + 1
            Cells(j, 5) = t(i)
            Cells(j, 4) = c.Offset(0, -1)
        Next
    End If
Next

End Sub

A+
kjin

 

[job75]

Re : Transformer ligne en colonne

Bonsoir infotun, kjin,

La macro est plus longue car elle fait aussi du formatage :

Sub Transformer()
Dim lig As Long, i As Long, j As Integer, tablo(), n As Long
Application.ScreenUpdating = False
lig = [A1].End(xlDown).Row 'dernière ligne
'---Détermination du tableau de valeurs---
For i = 2 To lig
  For j = 0 To UBound(Split(Cells(i, 2), ","))
    ReDim Preserve tablo(1, n)
    tablo(0, n) = Cells(i, 1)
    tablo(1, n) = Split(Cells(i, 2), ",")(j)
    n = n + 1
  Next
Next
'---Formatage---
Range(Cells(lig + 3, 1), Cells(65536, 2)).Clear
Columns(1).HorizontalAlignment = xlCenter
With Cells(lig + 3, 1).Resize(n, 2)
  .Borders(xlEdgeLeft).LineStyle = xlContinuous
  .Borders(xlEdgeTop).LineStyle = xlContinuous
  .Borders(xlEdgeBottom).LineStyle = xlContinuous
  .Borders(xlEdgeRight).LineStyle = xlContinuous
  .Borders(xlInsideVertical).LineStyle = xlContinuous
  .Borders(xlInsideHorizontal).LineStyle = xlContinuous
  '---Remplissage---
  .Value = Application.Transpose(tablo)
End With
End Sub

A+