Automate Copy and Paste Funtion based on Cell Value 1

[Chuck712]

I have a project involving a contest where people are given 1 credit for each 2 persons that they sign up as members. I am able to bring the counts of those total into Excel and get a value of how many times a person should be entered into the drawing for the grand prize. What I would like to do is to copy and paste that persons name and address back into the spreadsheet based on the value in the cell. For example
Joe Smith 123 Main St. 3

Would come back
Joe Smith 123 Main St.
Joe Smith 123 Main St.
Joe Smith 123 Main St.

I am not sure if this can be done, but I thought I would throw it out there.
Chuck

 

[Zathras]

Since this isn't the VBA forum, here is a non-VBA way to handle your data.

I have assumed that the names and addresses are concatenated together into one cell in column "A", with the counts in column "B". If that is not the case, you should do that as a first step. Then you can set up formulas as shown below. to give you multiple names and addresses spread across multiple columns. Do a copy and paste-special-values to replace all of the formulas with simple text, then sort each column "C" thru however many you need to bring all of the non-blank names together in the column. Finally, cut and paste each column "C" thru whatever into column "A"

C1: 1
D1: 2
E1: 3
F1: 4

(etc. as far as you need)

C2: =IF($B2>C$1,$A2,"")
D2: =IF($B2>D$1,$A2,"")

(etc. copy the formula as far as you need)

It would be quite simple to build a new sheet as you described using VBA (assuming you know a little VBA so as not to get lost in the details). If you do know some VBA, you should re-post on the VBA forum ( forum707 ) with full details of how your data is structured (i.e. all in one column, split across multiple columns, etc.) and someone will be glad to write the code for you.

 

[Chuck712]

Thanks. I will try this method and post over on the VBA forum as well.

 

[DaleWatson123321]

Chuck,

I've created a model for you (using VBA) that does exactly as you described.

If you'd like the file, email me and I'll send the file via return email.

I hope this helps

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Chuck712]

Thank you. This was the most helpful of all