Okay.
Best I can do within the limits of the data supplied.
1/. I have added conditional formatting to sheet "Final" to detect some errors.
2/. If after importing your data any row in sheet "Final" shows yellow, then run this macro
Option Explicit
Sub CleanData()
Dim LastRow As Long, RowNo As Long
With Sheets("Data")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
LastRow = Int(LastRow / 10) * 10 + 1
For RowNo = 11 To LastRow Step 10
If Not IsNumeric(Left(.Range("A" & RowNo), 1)) Then
If Not UCase(Left(.Range("A" & RowNo), 6)) = "PO BOX" Then
.Range("A" & RowNo + 1 & ":A" & RowNo + 8).Cut .Range("A" & RowNo)
End If
End If
Next
End With
End Sub
3/. Ihave retained the formulae with some slight modification, these could be improved, but if they work leave them as they are.
I suspect that you will probably want to delete sheet "Data" and have only values in sheet "Final"
To best acheive this I would Copy and PasteSpecial > Values sheet "Final" to a new sheet then export that sheet to a new workbook, retaining this workbook as a template.
Hope this helps
Bookmarks