Excel - create new rows and parse a list of call signs names against the postcode

gregfthompson used Ask the Experts™
The example is a list of call signs for radio & tv stations
On each row there are 5 columns containing information for that postcode
Column D contains a list of postcodes relevant to the call signs that are in the same row in column E.

The requirement is to create as many new rows for the postcode as there are call signs, and copy the contents of the postcode rows rows from A to D into the new rows, and then copy each call sign separately into column E beside the beside the postcode.
Rows 2 to 8 from Base worksheet have been copied manually into rows 2 to 101 in Required worksheet.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant

Try this, it puts the result in a new sheet but that can easily be changed.
Option Explicit

Sub FlattenData()
Dim ws As Worksheet
Dim arrIn As Variant
Dim arrOut() As Variant
Dim arrCallSigns As Variant
Dim I As Long
Dim J As Long
Dim K As Long
Dim cnt As Long

    arrIn = Sheets("Base").Range("A1").CurrentRegion
    For I = LBound(arrIn) + 1 To UBound(arrIn)
        arrCallSigns = Split(arrIn(I, 5), ",")
        For J = LBound(arrCallSigns) To UBound(arrCallSigns)
            ReDim Preserve arrOut(1 To 5, cnt)
            For K = 1 To 4
                arrOut(K, cnt) = arrIn(I, K)
            Next K
            arrOut(5, cnt) = arrCallSigns(J)
            cnt = cnt + 1
        Next J
    Next I
    Set ws = Worksheets.Add
    ws.Rows(1).Value = Sheets("Base").Rows(1).Value
    ws.Range("A2").Resize(cnt, 5).Value = Application.Transpose(arrOut)
End Sub

Open in new window


Thank you.
Worked perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial