I need some expert advice...don't know if this is something I can tackle in Excel or if it requires more advanced programming.

Let's say I have a spreadsheet with 1,000 rows of data representing prizes to be given out to different people. I know how many of the prizes should go to each person (283 to John, 42 to Mary, etc.). I would like to be able to enter the total number of prizes for each person and have Excel input the names into each row, in my "Winner's Name" column. (So if I were then to do a Pivot table, I could summarize to see that John has 283 prizes, Mary 42. etc.) Currently I am typing or pasting "John" on each row where he gets the prize, then going to the next person and typing/pasting until I have all the rows filled in with a name. I'd like to automate it so it's not only faster but also less prone to mistakes.

If I can do this, there is one other twist I would like. In addition to inputting just the total number of prizes, I may sometimes want to specify specific prizes for certain people. For example, maybe I'd like to ensure that John receives 4 candy bars (out of 42 candy bars) but otherwise I don't care about which prizes go to whom and the system can fill in the rest of the rows automatically (based on the totals I originally submitted for each person).

Thanks for your help!