Duplicate names to an array, then make random selection

Apologies about thread title, it's difficult to narrow this question down enough to a concise description!

I am trying to create a formula to select a name at random from an array, where each name can appear x number of times.
The issue I have is that the Names are not split out into a list x times, rather they are held just once in a table with the number of times they should occur in the adjacent cell.

So as an example... in cells A1:A3 I have the names Huey, Dewey and Louie. And in cells B1:B3 I have the numbers 1, 2 and 3, representing how many times each name should occur.
The aim is for a formula to duplicate them based on the occurrence number and then get them into an array along with a linear sequence number. e.g. {1,"Huey";2,"Dewey";3,"Dewey";4,"Louie";5,"Louie";6,"Louie"}
I can then use RANDBETWEEN to generate one of the the sequence numbers and just lookup the corresponding name.

At least that's my thinking so far as to how I approach this. It may well be over the top and/or the wrong approach, so I'm certainly open to changing direction. I'm aware that I could use VBA to do this a lot easier than a formula, but I'd rather use a formula/s if at all possible.

Any help or ideas welcome. Thanks.

See attached file.

I moved your names down one row, so I can put zero in C1 and this formula in C2:

=B2+C1

which when copied down gives me a cumulative count. Then I used this formula in E1:

=IF(ROWS($1:1)>MAX(C:C),"",ROWS($1:1))

and this one in F1:

=IF(E1="","",INDEX(A:A,MATCH(E1-1,C:C)+1))

and copied these down to row 10, so that you could vary the numbers slightly to verify the formulae work ok - you can copy down further if you need to.

Hope this helps.

Pete

in terms of a single cell calc, using A1:B3 as outlined:

Formula:

Please Login or Register  to view this content.

however, given use of OFFSET this is Volatile.

Thanks both. I'll have a play around see what's most suitable.