As ThumbsUp2, a macro would work well with this.
I have this VBS script that might help.
It takes input file of input.xls.
It expects two columns in the spreadsheet, column A is just text (could be your address) and column B is a number
The output is a csv file which (hopefully) produces what you're after, than can be imported into a spreadsheet.
Here's the file:
----------------------------------
strFileNameIn = "input.xls"
StrFileNameOut = "output.csv"
On Error Resume Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
strCSVFileIn = objFSO.getAbsolutePathName(".") & "\" & strFileNameIn
strCSVFileOut = objFSO.GetAbsolutePathName(".") & "\" & strFileNameout
If objFSO.FileExists(strCSVFileOut) Then
objFSO.DeleteFile(strCSVFileOut)
End If
If objFSO.FileExists(strCSVFileIn) Then
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Workbooks.Open(strCSVFileIn)
intWorkSheets = objExcel.ActiveWorkbook.Sheets.Count
intObjCount = 0
For i = 1 To intWorkSheets
Set objWorkSheet = objExcel.ActiveWorkbook.Worksheets(i)
intRow = 1 'Starting Row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Do until objWorkSheet.Cells(intRow,1).value = ""
strColA = Trim(objWorkSheet.Cells(intRow,1).Value)
strColB = Trim(objWorkSheet.Cells(intRow,2).Value)
For j = 1 To cint(strColB)
Set objOutStream = objFSO.OpenTextFile(strCSVFileOut,8,True)
Err.Clear
objOutStream.WriteLine(strColA)
Next
Set objOutStream = Nothing
intRow = intRow + 1
intObjCount = intObjCount + 1
Loop
Next
Set objWorkBook = Nothing
Set objWorkSheet = Nothing
objExcel.Quit
WScript.Echo "All Done"
Else
WScript.Echo strCSVFileIn & " does not exist."
End If
----------------------------------