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. HELP!!

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) Next 'Set first cell for next rep furstCell = furstCell + repSales Next End Sub

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History