Question Locked

Formula to multiply & copy an Excel row by no. stated in another column

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I am looking for a sum or formula that will enable me to copy an address row in Excel by the number of times stated in the end column.

For example, Joe Bloggs needs 5 copies of our magazine but his details are only on our spreadsheet once. Is there a sum which will copy and paste the row which has his address details in by the number of copies we want him to get (5 in this case).

Any help or advice with this would be VERY much appreciated.

Thanks,
Michelle.

5 replies

by thumbsup2

... but, by the time you learn the macro language (VBA) and get it to work correctly without going into an endless loop, it would probably be faster to search/copy/paste by hand yourself.

There is no built-in function to do this for you.

Are you using Mail Merge to create labels for the magazines based on the contents of this spreadsheet? If so, you might look into using a macro here to look at that field and if the number is greater than 1, print extra labels rather than duplicating rows in the spreadsheet itself. That way, if Joe Bloggs wants to change his number of subscriptions, it's an easy fix.

by michelledoust

We're sending out our magazines through a mailing company that poly wraps them... They will use the spreadsheet we provide them with to create the carrier / address sheets.

They have asked that if an addressee requires more than one copy of our mag, they are entered onto the spreadsheet seperately... We could just provide the addresses and state how many each one gets but this would mean they'd have to manually sort it the data and there's an extra charge for that which we can't really justify.

by bizzo

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
----------------------------------

This discussion is closed to new replies.