auto populate #2

I am trying to figure out how to take a list of names and fill a time slot 
list randomly with those names.  each name can only be used up to 2 times.  
its around 14-16 names with 25 -35 slots.

Any suggestions
0
jdog (1)
10/15/2005 4:14:27 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
384 Views

Similar Articles

[PageSpeed] 48

Sounds like a homework problem. Are you trying to solve this with VBA or with formulas? How far have you gotten with it?

-- 
RMC,CPA


"jdog" <jdog@discussions.microsoft.com> wrote in message news:E06B75BC-B057-4280-B64D-8EE8FE8255F9@microsoft.com...
I am trying to figure out how to take a list of names and fill a time slot
list randomly with those names.  each name can only be used up to 2 times.
its around 14-16 names with 25 -35 slots.

Any suggestions 


0
rchoatecpa (136)
10/15/2005 4:23:41 PM
I agree that it sounds like homework / assignment, but possible is:

Assuming that your list of names is in B1 to B16, and that column A is
free.

insert 
=rand()
in a1 to a32 (ie, twice the name range 14 to 16)

then use


=INDEX(B$1:B$16,IF(MATCH(SMALL(A$1:A$32,ROW()),A$1:A$32,0)<17,MATCH(SMALL(A$1:A$32,ROW()),A$1:A$32,0),MATCH(SMALL(A$1:A$32,ROW()),A$1:A$32,0)-16))

- explained as Index (select) b1:b16 depending on:
use the smallest of A1 to A32 (minus 16 if applicable) according to
the current row number (thus the 5th row selects the 5th smallest)

This does not ensure that all names are used at least once.
It also is random and does not prevent a name being selected
consecutively.

Hope this helps.


R. Choate Wrote: 
> Sounds like a homework problem. Are you trying to solve this with VBA or
> with formulas? How far have you gotten with it?
> 
> --
> RMC,CPA
> 
> 
> "jdog" <jdog@discussions.microsoft.com> wrote in message
> news:E06B75BC-B057-4280-B64D-8EE8FE8255F9@microsoft.com...
> I am trying to figure out how to take a list of names and fill a time
> slot
> list randomly with those names.  each name can only be used up to 2
> times.
> its around 14-16 names with 25 -35 slots.
> 
> Any suggestions


-- 
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=476507

0
10/16/2005 1:42:03 AM
Hi,

If you have 16 names and more than 32 time slots, some names WILL have to be 
used more than twice.  Let us imagine that you have 32 time slots and 16 
names.

     Place the time-slot-list in A1:A32 and the name-list in B1:B16.
     Copy B1:B16 and paste onto B17:B32. 
     Create a helper column C1:C32 with random numbers.  For this, in C1 
enter 
=RAND() and fill-down the formula to C32.
     Copy the range C1:C32, "Edit"-->"Paste Special"/"Values" onto C1:C12 
itself.
     Now select B1:C32 (i.e., the name and random number columns; do not 
select column A) and sort by column C.

Regards,
B. R. Ramachandran   


"jdog" wrote:

> I am trying to figure out how to take a list of names and fill a time slot 
> list randomly with those names.  each name can only be used up to 2 times.  
> its around 14-16 names with 25 -35 slots.
> 
> Any suggestions
0
10/16/2005 4:59:04 AM
Hello,

I suggest to take my UDF UniqRandInt() from www.sulprobil.com.

If your names are listed in A1:A16, for example, then select cells B1:B25 
and enter
=INDEX(A1:A16,UniqRandInt(16,2))
as array-formula (with CTRL+SHIFT+ENTER).

Please keep in mind that my function returns an error value if the length of 
your list is less than 16 * 2 as B. R. Ramachandran already pointed out.

HTH,
Bernd 


0
bplumhoff (22)
10/21/2005 10:51:34 PM
Reply:

Similar Artilces:

Populate a cell if values in cell 1 and cell 2 match cell 3 and 4
I need help with a formula please. I need to search through a column of data and populate a cell if it meets a condition. For example: IF FirstWorksheet!A2 = SecondWorksheet!ColumnAx and FirstWorksheet!B2 = SecondWorksheet!ColumnBx Then populate SecondWorksheet!Cx with value from FirstWorksheet!C2 I have to match up a column of data in one worksheet with data in 2 columns in another worksheet. Help plz. Thanks T- A multi-criteria index n match (array-entered) should work here In Sheet2, Copy n paste this into C2's formula bar, then array-enter the formula by pressing CTRL+...

Auto Filter problem
I am using the auto filter for a particular column to sort out differen medical programs. When I click the drop down and click a program, i doesnt show me ALL the lines that say this particular program. Why is this? -- Message posted from http://www.ExcelForum.com Hi maybe some hidden characters in these lines 8e.g. additional space characters, etc.) -- Regards Frank Kabel Frankfurt, Germany "jkb724 >" <<jkb724.19rasx@excelforum-nospam.com> schrieb im Newsbeitrag news:jkb724.19rasx@excelforum-nospam.com... > I am using the auto filter for a particular column to so...

Auto-Editing
Word 2003 has a very annoying default. It assumes too much! If I reformat a few words in a document, it changes the entire document to that formatting. Grr-rrr-r! I have to constantly press undo to get what I want. I know there's a feature that's causing this annoyance but I can't find it to turn it off! Can anyone help, please? See http://word.mvps.org/faqs/formatting/wholedocumentreformatted.htm. -- Stefan Blom Microsoft Word MVP "Connie Martin" <ConnieMartin@discussions.microsoft.com> wrote in message news:CD959D82-F81B-4A9D-993E-73...

Windows 2000 GP installation #2
GP 9.0 Fully Patched/Updated Windows 2000NT console. When installing the integration manager this error pops up when copying files: " The windows installer service cannot update the system file C: \WINNT\system 32\msscript.ocx because the file is protected by windows. You may need to update your operating system for this program to work properly. " Clicking okay resumes the install and everything seems to proceed just fine. When running MS Dynamics GP a login and server can be selected but when you attempt to login to a company we get "The databse setup has not been co...

CHeaderCtrl #2
MFC is just the thing for me it seems, plently of vexation to while away the hours. *sigh* I am trying to inser a bitmap in the fourth column of my header control, attached to a list ctrl. After reading the docs and codeproject resources, here's what I came up with: void MyViewClass::OnInitialUpdate() { [creation code for List Ctrl, "List"] ..... CHeaderCtrl* pHead = List.GetHeaderCtrl(); CBitmap bm; bm.LoadBitmap(IDB_BITMAP3); HDITEM hdi; pHead->GetItem(4,&hdi); hdi.mask |=HDI_BITMAP|HDI_FORMAT; hdi.fmt |=HDF_BITMAP; hdi.fmt &=~HDF_STRING; ...

Auto-Calculate
I have a spreadsheet that utilize the stock quote add-in from MSN. On a daily basis I refresh the add-in functions for the Highs, Lows, Last, Volume, etc. On occasion the spreadsheet reverts to manual caculation settings, and I have to press the F9 even after I updated the quotes. I've always preferred the any spreadsheets I open, blank or otherwise, the automatic setting selected for calculations. I consider this an annoyance. Has anyone experienced this glitch? Is there a fix for it? Ron_D Ron Excel takes the calculation mode from the first workbook that opens in a session. Sub...

addresslists and cantacts #2
Yes, I mean custom recipients. We need to configure our envirement so that every user can view the 25 k contacts in a addressbook. Is there any disadvantage, if I add 25k objekts to the active directory? Could it become slowly or so? Wy should I favorite the active directory integrated method? I could also create all contacts in a public folder? Whats the crucial differnce between the two ways? Thanks for your answer! :-) ...

auto copy self
How do I arrange for Outlook to automatically copy myself on every new, reply or forward email? If you mean keep a copy of all sent, see settings under Tools/Options/Email Options and Advanced Options David "rich" <steamuk@aol.com> wrote in message news:055c01c3664f$dadecd30$a001280a@phx.gbl... > How do I arrange for Outlook to automatically copy myself > on every new, reply or forward email? ...

Copy and paste #2
I am trying to copy and past a document or any picture in publisher 2003 and it is pasting the item I copied and pasted last. I closed Publisher and opened it again, but it still pasted the last item pasted before!!! Help!!! The issue you describe is unknown. Try re-booting your computer and trying it again. -- "If you don't know where you are going, any road will take you there!" ...

Runing 2 things at the same time
Hi, I using Access 2003 I have a module. The module will open a form when it was executed and will close the form once finishes. The from has a label to show the user the system is still processing by using the form timer. The form works fine working on itself. Once i use the module to run the code, the form loaded but does not show anything and close once finished Below is the example of Code Module: Public function StartModule() Docmd.OpenFrom "ProcessBar" 'Code that is need for the module ... ... ... Docmd.Close acFrom "Proces...

Inventory Wizard #2
We tried running the inventory wizard to change item costs, but it does not seem to affect supplier costs. The problem, then, is that if the store places a purchase order, they are not using the new cost. Is there a way to update the cost and supplier cost at the same time? Rick this is the most crazy thing i have ever seen a software do. I dont understand that when you change "supplier cost" inside of a PO at HQ why it doesnt change the "supplier cost" item. That makes no sense to me at all. My RMS vendor says the only way you can do that is by going to each item...

Auto correct or Auto complete
We are all familiar witht he auto correct or auto complete functions found in MS applications. Of course that means it is available in Access also. My question is this ... can this function be replicated within the DB, so that the set of auto correct items are part of the package rather than something that would have to be set up on each individual desktop system? It's use, for me, would primarily be within a memo field where medical abreviations, used as a shortcut, would convert to the true meaning as the user types them in. Ex: "prn" without quotes would change to. &qu...

Auto Scale Chart Macro -- Any improvements please?
This is the best way I've found to correctly scale a chart -- the only way I found to exclude a chart interpolating with #NA cells was to delete those cells containing #NA, and run the chart. This takes a long time on my computer. Wondering if there is a better way. This macro is directly taken from 2 excel mvps and modified; i believe a mehta and ron bovarty, but could be wrong. Thanks for taking a look. Sub AutoScaleYAxes() Dim ValuesArray(), SeriesValues As Variant Dim Ctr As Integer, TotCtr As Integer Application.Run "Extend_Stock_Data" Applic...

Comment Box #2
Hi, (Excel 2002, Win XP) Can I set up Excel so that when I write a comment it does not show the User name at all in the comment box? I tried to just clear the name in the option under Tools/Options but it then defaults to User as defined in Windows. Tx, S Hi See Debra's tip http://www.contextures.com/xlcomments02.html#User -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "saturnin02" <saturnin02@hotmail.com> wrote in message news:uQSXCA$%23DHA.2348@TK2MSFTNGP09.phx.gbl... > Hi, > (Excel 2002, Win XP) > Can I set up Excel so that whe...

OWA auto login
I am trying to use the credentials from the forms authentication login to automaticaly log in to Outlook Web. I redirect to a URL with this format: http://UserName:Password@mailserver/exchange I have seen this example in other posts but this does not work for me. I still get the Outlook Windows Login prompt. Perry Perecli Manole wrote: > I am trying to use the credentials from the forms authentication > login to automaticaly log in to Outlook Web. I redirect to a URL with > this format: http://UserName:Password@mailserver/exchange > I have seen this example in other posts but t...

Auto look through subfolders
Please help. I can write a macro that will extract data from all excel files in a specific folder. What I need to be able to do is write a macro that will automatically extract data from excel files within folders and sub folders and sub sub folders etc For example the data is all held on the following directory: H:\development forms\2006 cost sheets\ And within that directory by customer - for example Tesco, Boots, Asda etc. These customers are then subdivided into product ranges - eg 1,2,3,4 etc and some futher subdivided by national or exclusive. All the excel files are in exactly th...

Exchange 2007 Beta 2 question
Hi I have the mailbox, CA, hub and UM on one server. I am trying to create a new email life cycle policy, but the option to do this cannot be found. I right click on the mailbox icon in EMC under "organization" but the option is not listed. The only listed options are. Also the tab in ESM for this is not there New address list New managed custom folder new managed fodler mailbox policy new offline address list On Wed, 23 Aug 2006 13:58:02 -0700, skip <skip@discussions.microsoft.com> wrote: >Hi > >I have the mailbox, CA, hub and UM on one server. I am trying to...

2 X 2 Splitter Windows #2
What is wrong with the following code? I am trying to create a 2 X 2 splitter window. It should look like this. CMyTreeView | CMyListView __________________|___________________ | CPictureView | CDetailView It compiles correctly but when I run it I get an error in the WinSplit routine of MFC. if(!m_wndSplitterWnd1.CreateStatic(this,1,2)) return FALSE; if(!m_wndSplitterWnd1.CreateView(0,0, RUNTIME_CLASS(CMyTreeView), CSize(0,0), pContext)) return FALSE; if(!m_wndSplitterWnd2.CreateStatic(&m_wn...

Any way to get rid of an auto-suggestion in the login dialog box, for Exchange/OL using RPC over HTTP?
A client was using RPC over HTTP to two different Exchange servers (OL prompting for profile on startup). Both profiles were set up to prompt for credentials upon connection to Exchange. There's now only one Outlook profile needed; the other is gone and OL isn't prompting for the profile selection any longer, which is fine. However, Outlook is remembering domain1\user and domain2\user in the login dialog box, and I'd love to be able to get rid of the no-longer-valid credential dropdown suggestion. (The computer is not a member of a domain and hence always needs to be prom...

Auto date
I am trying to create a field in a form that if anything is changed on it, it will automatically update the date to current date. Is this possible? Sojaminc wrote: >I am trying to create a field in a form that if anything is changed on it, it >will automatically update the date to current date. Is this possible? Make sure the last changed date/time field is in the form's record source table/query. Then use the form's BeforeUpdate event: Me.lastchanged = Now -- Marsh MVP [MS Access] Hi - You can do this in the On Dirty event of the form, which fires as soon as you m...

remove macro #2
Hi every time I start an excel workbook I have made I get the security warning about running macros but as far as I can tell there are no macros in the workbook. When I go to tools/macro there are no macros visible in the box that appears. So what is causing the security message to appear? And how can I stop it? If I set the security level to low the message does not appear. Windows XP Pro SP2 and Office 2003 Thanks Ron Press ALT+F11 to open the VBA editor. Delete any text in any of the modules under "Microsoft Excel Object" and delete any modules under "Modules"...

2 workbooks open
Hi - A co-worker created a spreadsheet - over the course of a few weeks, several tabs were added. Recently, when I received the spreadsheet via email, 2 identical workbooks opened. The 1st workbook was called 'Data (1)' and the 2nd was called 'Data (2)'. There wasn't a macro on either spreadsheet - I'm at a loss to why 2 identical workbooks opened. Any ideas or suggestions are appreciated. Regards, Close the second instance of the Workbook and then Save the file. Meg wrote: > Hi - > > A co-worker created a spreadsheet - over the cou...

An auto reply to an auto reply!
Had the funniest situation yesterday. Somebody emailed a public folder we have set up. On there we have a rule to send a reply saying thanks for your email -someone will be with you shortly (we have to have this message on - it is for some website that recently went live). Now that auto reply went back to the guy who emailed - who must have sent it from a public folder himself (as he forwarded the original email from a public folder). The guy received our auto reply - and promptly his email system sent an auto reply to us. As is the way it is set up - our email system sent him an auto ...

ETRN #2
Hey again, Yesterday I posted a question about setting up a trust on exchange so that my exchange server would accept email from a backup email server (msg:Backup email denied 4 out of 5). This has worked fine. So now I want to setup exchange so it sends an ETRN every hour or whatever to the mail server requesting any mail that the backup server has be delivered. I followed the help instructions and setup a connector in the System Manager and set it t do an ETRN every hour to the IP of the secondary mail server for the domain but the hosting company said they are not seeing the ETRN...

importing from excel #2
Help! I'm trying to import information from an excel spreadsheet to create contacts in Outlook & once I run the import wizard it makes a contact for EACH cell of data instead of migrating each row as one contact. gls ...