Need code to reconstruct a list from raw data

[Rudi]

Hi,

I would normally try to code this myself but lots to do today, so if anyone has some time to spare I'd appreciate the help.
Please see attached to clarify.

Many Thanks

[Rudi]

I have found time to set up a macro, but it probably could be optimised since it will be run on a regular basis..
Also the addresses start and end with commas...

Any optimization and improvements will be appreciated.

TX

[HansV]

Nice solution!

One small improvement: you don't have to repeat computing Range("D" & Rows.Count).End(xlUp) - you only need to increase the row when you encounter "Acc".
The following version also removes the leading ", ":

Code: Select all

Sub MakeList()
    Dim rData As Range
    Dim rC As Range
    Dim i As Integer
    Dim sAddr As String
    Dim r As Long
    r = 1
    Set rData = Range("A1").CurrentRegion
    Application.ScreenUpdating = False
    For Each rC In rData.Columns(1).Cells
        If rC.Value = "Acc" Then
            r = r + 1
            rC.Offset(0, 1).Copy Range("D" & r)
        End If
        If rC.Value = "Name" Then
            rC.Offset(0, 1).Copy Range("E" & r)
        End If
        If rC.Value = "Delivery Address" Then
            For i = 1 To 10
                If rC.Offset(i, 0).Value = "Acc" Then
                    Exit For
                End If
                If rC.Offset(i, 0).Value <> "" Then
                    sAddr = sAddr & ", " & rC.Offset(i, 0).Value
                End If
            Next i
            Range("F" & r) = Mid(sAddr, 3)
        End If
        sAddr = ""
        If rC.Offset(0, 1).Value = "Postal Address" Then
            For i = 1 To 10
                If rC.Offset(i, 0).Value = "Acc" Then
                    Exit For
                End If
                If rC.Offset(i, 1).Value <> "" Then
                    sAddr = sAddr & ", " & rC.Offset(i, 1).Value
                End If
            Next i
            Range("G" & r) = Mid(sAddr, 3)
        End If
        sAddr = ""
    Next rC
    Application.ScreenUpdating = True
    MsgBox "Data compiled.", vbInformation
End Sub

[Rudi]

Nice solution!

Wow...that's a BIG compliment coming from you.
I have mind to screenprint this page, print it and frame it on my wall.

Many TX for taking the time to review the code and add improvements. I'll scrutinize your changes and take notes.

Cheers