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

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.
2017-11-21-Radio---TV-call-sign-to-.xlsx
gregfthompsonAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Greg

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

0
 
gregfthompsonAuthor Commented:
Thank you.
Worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.