CODEWITHSUNDEEP
excelexcel-formulavba
sanjeev
sanjeev

Reputation: 97

Excel mapping from column to column

I have data in following format in Excel: Input:

Col1       | Col2
A1;A2;A3;A4|B1;B2;B3;B4
A5;A6      | B5;B6
A7;A8      | B7;B8

I want data to be in subsequent formatting i.e Output

Col1 | Col2
A1 |B1
A2 |B2
A3 |B3
A4 |B4
A5 |B5
A6 |B6
A7 |B7
A8 |B8

I tried a lot but didn't find any solution about How to frame this question or do it in excel. Every Help/Guide/Link will be highly appreciated. Thanks in advance.

Upvotes: 0

Views: 258

Answers (1)

JohnyL
JohnyL

Reputation: 7142

Sub Test()

    Dim arr1(), arr2(), split1, split2
    Dim rng As Range, rngRow As Range
    Dim x As Integer, z As Integer

    'Assuming data starts from A1 cell
    Set rng = Range("A1").CurrentRegion

    For Each rngRow In rng.Rows
        split1 = Split(rngRow.Cells(1), ";")
        split2 = Split(rngRow.Cells(2), ";")
        For x = LBound(split1) To UBound(split1)
            z = z + 1
            ReDim Preserve arr1(1 To z)
            ReDim Preserve arr2(1 To z)
            arr1(z) = split1(x)
            arr2(z) = split2(x)
        Next
    Next

    Range("D1").Resize(UBound(arr1), 1).Value = Application.Transpose(arr1)
    Range("E1").Resize(UBound(arr2), 1).Value = Application.Transpose(arr2)


End Sub

Upvotes: 1

Related Questions