Excel 2003 multiplying out single line items

June 29, 2009 at 21:12:43
Specs: Windows XP
I'm running an incentive that will have a raffle drawn at the end of the incentive, however the data I receive each week comes with the persons name and how many policies they have sold for week 1, week 2 etc. e.g. Aaron in column A and 10 in column B, 7 in column C and so on. What I want is to have Aaron written 17 times in this example and then continue to do the same for the other salespeople. BTW there could be over 2000 salespeople each one doing up to several hundred sales each over the 4 week period. I will then have a random selection process.


See More: Excel 2003 multiplying out single line items

Report •

June 30, 2009 at 09:18:36
Try this, assuming your list of reps start in Sheet1!A1, you want the expanded list of names to start in Sheet2!A1 and you won't have more than 16,777,216 total sales.

Sub SaleForce()
'Initialze first cell variable
 furstCell = 1
'Determine how many sales reps there are
 numReps = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through reps
  For repRow = 1 To numReps
'Sum Sales for rep
   repSales = Application.WorksheetFunction.Sum(Range("B" & repRow & ":E" & repRow))
'Loop through next set of cells based on number of sales
    For nxtCell = furstCell To furstCell + repSales - 1
'Place rep name in next cell
     Sheets(2).Cells(nxtCell) = Range("A" & repRow)
'Set first cell for next rep
     furstCell = furstCell + repSales
End Sub

Report •
Related Solutions

Ask Question