CODEWITHSUNDEEP
sql-server-2012excel-2010
James Youkhanis
James Youkhanis

Reputation: 45

Split data in Excel

I currently have a spreadsheet that looks like this.

Client_ID             State
1091                  AR,FL,HI,LA,MI,OR

This is the outcome I would like, every state to be spilt into a new row.

Client_ID             State
1091                  AR
1091                  FL
1091                  HI
1091                  LA
1091                  MI
1091                  OR

Upvotes: 1

Views: 53

Answers (1)

Gary's Student
Gary's Student

Reputation: 96771

This assumes that the input is in Sheet1 and the output will go in Sheet2 in columns A & B.

Run this small macro:

Sub ReOrganizeData()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim c1 As String, c2 As String
    Dim N As Long, i As Long, K As Long
    Dim j As Long
    Dim v1 As Variant, v2 As Variant
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    c1 = "A"
    c2 = "B"
    N = s1.Cells(Rows.Count, c1).End(xlUp).Row
    K = 2

    For i = 2 To N
        v1 = s1.Cells(i, c1).Value
        v2 = s1.Cells(i, c2).Value
        ary = Split(v2, ",")
        For j = LBound(ary) To UBound(ary)
            s2.Cells(K, c1).Value = v1
            s2.Cells(K, c2).Value = ary(j)
            K = K + 1
        Next j
    Next i
End Sub

Upvotes: 0

Related Questions