How to copy and fill a text consecutively in coloumn to "X" number of times provided

[bmbalamurali]

Hi,

My objective is to fill names in a column consecutively to "X" number of times, Example: if i give John 5 times and Woody 6 times....
Macro should fill john 5 times and consecutively it has to fill Woody 6 times in vertical order in column.

Name and "X" number will change daily..

I have explained my need in the sample excel i attached, If anything is not clear please come back....

Thanks and cheers friends.

[Pete_UK]

See attached file for a formula-based solution.

Put zero in H6 and this formula in H7:

Please Login or Register  to view this content.

then copy down to the bottom of your data to give a cumulative count of the names (shown in blue). Then put this formula in A4:

Please Login or Register  to view this content.

and this one in B4:

Please Login or Register  to view this content.

then copy both of these down as far as you think you need them.

Hope this helps.

Pete

[bmbalamurali]

Hi Pete,

Thanks for your simple and clear explanation of solution to my query.

i ask you a favor, could you please explain what does this formula doing, actually how it is working? i know the purpose of IF, Match, Index functions, but in this how its working please explain?

=IF(ROWS($1:1)>MAX($H:$H),"",INDEX(E:E,MATCH(ROWS($1:1)-1,$H:$H)+1))

Thanks Pete...

[AB33]

Try the attached code

[bmbalamurali]

Hi AB33 & Pete,

I tried the formulas given and its working fine, i forgot to mention this in my previous reply....Thanks...

Request your explanation for me for the functions given...

Great appreciation and 1000 thanks to Excel forum and to you guys.. really a great work...

[Pete_UK]

The term ROWS($1:1) returns 1 in the first cell it is in, but when the formula is copied down that term becomes ROWS($1:2), then ROWS($1:3), then ROWS($1:4) etc. on successive rows, which return the values 2, 3, 4 and so on, so that allows us to set up an incremental counter within the formula. Clearly, we don't want to return any data beyond the number of repeats that we have, so the first part of the formula ensures this, returning a blank "" if we have copied the formula too far for our needs.

The MATCH part of the formula is determining which row we should get data from by comparing the counter with the cumulative values in column H - this ensures that we will get 5 successive values pointing to row 7, before getting 5 successive values pointing to row 8, and so on. INDEX just retrieves the data in column E on the row pointed to by the MATCH function.

Hope this helps.

Pete

EDIT: if this has solved your problem please mark the thread as Solved (use Thread Tools just above your first post). Also, click on the "star" icon in the bottom left corner of any post that has helped you so you can pass thanks on more directly to any poster that has helped (not only in this thread).

[bmbalamurali]

Thanks Pete, Understood fine....