+ Reply to Thread
Results 1 to 6 of 6

Randomly spread tasks over names

  1. #1
    Registered User
    Join Date
    11-26-2016
    Location
    Antwerp
    MS-Off Ver
    10
    Posts
    6

    Randomly spread tasks over names

    Hi all,

    I am working on an automatic work roster to help spread workload over my team members.

    With 5 team members present I can generate a list of tasks in the following form, normally the team has 7 members.
    The spreadsheet currently tells me how many people I need on which task

    Task 1 - 2 people
    Task 2 - 1 "
    Task 3 - 0 "
    Task 4 - 0 "
    Task 5 - 2 "

    I need to spread this randomly over a list of team members that are present.

    The expected result would be something in the form of the following:
    Person A - Task 1
    Person B - Task 5
    Person C - Task 2
    Person D - Task 5
    Person E - Task 1

    As an additional difficulty, I'm using google sheets to do this.

    Thanks for all help in advance

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,061

    Re: Randomly spread tasks over names

    First we need to generate a list of tasks to choose from...

    In Sheet1

    in B1:B5 put the Task numbers, Task 1, Task 2 etc
    in C1:C5 put the number of persons 2, 1, 0, 0, 2 (do not put the word "people" in this column)

    in A1 put 1
    in A2 put =A1+C1
    and copy down the column for however many people you have

    in D1:D5
    =IFERROR(VLOOKUP(A1,$A$1:$C$15,2),"")
    Column D is a list of the Task occurrences as defined by column C

    Source: https://www.extendoffice.com/documen...e-x-times.html

    ...now to pick from that list randomly without repeating...

    in E1:E5
    =RAND()

    in F1:F5
    =INDEX(D$1:D$5,MATCH(SMALL(E$1:E$5,ROW()),E$1:E$5,0))

    Column F is the Task number limited by the number of occurrences in column C
    The output you want is from column F which is the Task Number limited by its number of occurrences as defined by column C
    Last edited by Special-K; 11-28-2016 at 06:22 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    11-26-2016
    Location
    Antwerp
    MS-Off Ver
    10
    Posts
    6

    Re: Randomly spread tasks over names

    Hi special-k

    The random part is working perfectly,
    The first part isn't working correctlly (generated result gives 3 occurences of task 5 and 1 occurence of task 1)


    Thanks for the help

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,061

    Re: Randomly spread tasks over names

    Mm, I see what you mean.
    Will check it out further...

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,061

    Re: Randomly spread tasks over names

    Oops, I left out step 3 in that link.

    in B1:B5 put the Task numbers, Task 1, Task 2 etc
    in C1:C5 put the number of persons 2, 1, 0, 0, 2 (do not put the word "people" in this column)

    in A1 put 1
    in A2 put =A1+C1
    and copy down the column for however many people you have

    in D1 put 1
    and drag it down using Auto fill so it goes down for as many total occurrences as you want.


    in E1:E5
    =VLOOKUP(D1,$A$1:$B$5,2)
    Column E is a list of the Task occurrences as defined by column C

    Source: https://www.extendoffice.com/documen...e-x-times.html

    ...now to pick from that list randomly without repeating...

    in F1:F5
    =RAND()

    in G1:G5
    =INDEX(E$1:E$5,MATCH(SMALL(F$1:F$5,ROW()),F$1:F$5,0))

    Column G is the Task number limited by the number of occurrences in column C
    The output you want is from column G which is the Task Number limited by its number of occurrences as defined by column C

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,061

    Re: Randomly spread tasks over names

    UPDATE:
    If you enter more than the total amount in column D it repeats the last entry so change the following

    in D1 put
    =IF(ROW()>SUM(C$1:C$5),"",ROW())
    and drag it down

    in E1:E5
    =IFERROR(VLOOKUP(D1,$A$1:$B$5,2),"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Distribute names randomly
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 05-03-2016, 01:47 PM
  2. Replies: 4
    Last Post: 02-05-2016, 03:20 PM
  3. Randomly Pick Names?
    By racefan91 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2014, 02:15 AM
  4. Spread hours out evenly (or close to) over employees executing tasks in a WBS.
    By jbsitler in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-09-2012, 06:00 PM
  5. Matching Names to Tasks
    By souljive99 in forum Excel General
    Replies: 7
    Last Post: 06-02-2009, 10:07 AM
  6. [SOLVED] how do i create a spread sheet with randomly placed numbers 1-500.
    By sarasota in forum Excel General
    Replies: 1
    Last Post: 01-25-2006, 12:55 PM
  7. select names randomly
    By jroque in forum Excel General
    Replies: 1
    Last Post: 01-26-2005, 08:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1