VBA to Simplify the String Data

[sur]

Hello All,


I have a String data here,


i have Code 123, Start Date, End Eade (common), in Cities 1 2 3 4 5 6 (May Vary).


Code Start Date End Date HYD BNG CHN MUM
123 9/19/2018 9/19/2018 1 1 1 1
234 9/20/2018 9/19/2018 1 1 1
234 9/19/2018 9/19/2018 1 1
532 9/19/2018 9/19/2018 1 1 1




Required:


123 9/19/2018 9/19/2018 HYD
123 9/19/2018 9/19/2018 BNG
123 9/19/2018 9/19/2018 CHN
123 9/19/2018 9/19/2018 MUM
234 9/20/2018 9/19/2018 BNG
234 9/20/2018 9/19/2018 CHN
234 9/20/2018 9/19/2018 MUM
234 9/19/2018 9/19/2018 HYD
234 9/19/2018 9/19/2018 BNG
532 9/19/2018 9/19/2018 HYD
532 9/19/2018 9/19/2018 BNG
532 9/19/2018 9/19/2018 CHN


Thanks,
SUR

 

[JLGWhiz]

This will use a new sheet to store the formatted data.

Sub t()
Dim sh As Worksheet, ssh As Worksheet, c As Range, lc As Long, i As Long
Set ssh = ActiveSheet
Set sh = Sheets.Add(After:=Sheets(ThisWorkbook.Sheets.Count))
With ssh
    For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
        If c <> "" Then
            lc = .Cells(c.Row, Columns.Count).End(xlToLeft).Column
            For i = 4 To lc
                If .Cells(c.Row, i) <> "" Then
                    sh.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = c.Resize(, 3).Value
                    sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 3) = .Cells(1, i).Value
                End If
            Next
        End If
    Next
End With
End Sub

 

[sur]

No its Giving the Error.