Repeating items a specific number of times

**ninja0059** · 01-22-2008, 12:08 PM

Hello,

I am trying to create a formula that would provide a result a specific number of times based on a table in a seperate worksheet. Here is an example:

Person: # of times
John 2
Joe 2
Frank 2
Jack 1

Based on an IF statement, the cell should bring back John, the next cell down should bring back John, the third cell should bring back Joe as John has reached his limit of 2, and have this repeat until all criteria that meet the IF statement have been met.

**NBVC** · 01-22-2008, 03:56 PM

Well it's not the prettiest way but, here's one anyways...

First you need to download and install the morefunc.xll addin from this free website: http://xcell05.free.fr/english/

then put this formula in any cell, I will assume you put it in E1 and your original data is in A1:B4

Please Login or Register  to view this content.

after adjusting the ranges, you must confirm this formula with CTRL+SHIFT+ENTER keys...not just ENTER...you will notice { } brackets appear around the formula

that formula will concatenate all the names, including repeats, all in one cell.

Then in another cell, say F1.... apply this formula:

Please Login or Register  to view this content.

again, where E1 is the location of the Mconcat formula....

then copy down.

**ninja0059** · 01-23-2008, 11:45 AM

Thanks NBVC....

This seems to be working, however, maybe you can help with the next step. How would I be able to take a list of products, for example, and have each of them assigned to the individuals based on the number of times previously determined. For example

Apple - John
Orange - Jane
Peach - Hebert
Apple - John
Apple - John
Orange - Jane
Apple - Jack (since john has reached his limit of 3)
Orange - Janis (since jane has reached her limit of 2)

**NBVC** · 01-23-2008, 12:00 PM

Originally Posted by ninja0059

Thanks NBVC....

This seems to be working, however, maybe you can help with the next step. How would I be able to take a list of products, for example, and have each of them assigned to the individuals based on the number of times previously determined. For example

Apple - John
Orange - Jane
Peach - Hebert
Apple - John
Apple - John
Orange - Jane
Apple - Jack (since john has reached his limit of 3)
Orange - Janis (since jane has reached her limit of 2)

Not sure I understand.... please post a zipped Xl2003 workbook showing what you need to be done.

**ninja0059** · 01-23-2008, 01:50 PM

Here is a sample:

**NBVC** · 01-23-2008, 02:43 PM

See the attached...

I basically used the same concept I described about to get the items you see in columns I:K (these rows can be hidden).... make sure that you copy the formulas in J and K down as far as necessary to ensure all your data from E to G is covered.

Formula in I1:

Please Login or Register  to view this content.

Formula in I2:

Please Login or Register  to view this content.

both CSE confirmed

Formula in J1, copied down as far as you want:

Please Login or Register  to view this content.

Formula in K1, copied down as far as you want:

Please Login or Register  to view this content.

Then In B2, to extract your desired values, I inserted formula:

Please Login or Register  to view this content.

which must be confirmed with CTRL+SHIFT+ENTER and then copied down.

adjust all ranges to suit and re-confirm with CSE keys where necessary.

**ninja0059** · 02-07-2008, 03:55 PM

NBVC - The CTRL + SHIFT + ENTER is not working for me. Is there a setting I have to change to make this work?

**snasui** · 02-08-2008, 11:24 PM

Originally Posted by ninja0059

Here is a sample:

Another,

See attathed file.

Hope this helps.

------
snasui

**ninja0059** · 02-11-2008, 01:02 PM

Thanks for the additional sheet. However, my CTRL + SHIFT + ENTER still does not allow the formula to include the { } in it. In essence, the formula is now working, but stops after the first row.

**NBVC** · 02-11-2008, 01:09 PM

In order for you to get the { } around the formula, the formula in the cell must be "activated".... so click the cell with the formula and hit F2 so the cursor shows in the formula. Then hold the Ctrl and Shift keys down and hit Enter..