+ Reply to Thread
Results 1 to 9 of 9

Excel Formula - Group Assignment

  1. #1
    Registered User
    Join Date
    01-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    89

    Excel Formula - Group Assignment

    My Excel Sheet 1 has the following Data

    Sl.No(Column1) ,Employee Name(Column2) , Group Code(Column3) , Employee Group Number(Column4) .

    Sl.No and Employee Name has the data.

    In another Entry Sheet 3 data has to feed.
    1. Total Group:
    2. Group Start Number:
    3. Members per Group:
    [/B]
    This is my Excel book structure.
    ___________________

    Each Group has One Leader, One Assistant and 7-10 members. (E.g: Group :1+1+7)

    if we feed in Entry Sheet we enter Total Groups = 10, Group Start Number = 011 , Members per Group=7
    then Group Code column will be filled by Group1,Group2, ....Group 10 (Upto Total Groups)

    and the same in Employee Group Number column will be assigned as
    First 10 (Total teams) employees as Leader (Code will Prefix with LRwith Group Code 3 digit.) LR011,LR012,LR013...
    Next 10 employees as Assistant(Code will Prefix with ATwith 3digit Group Code.) AT011,AT012,AT013...
    (Both Leader and Assistant number will be same except the prefix)

    From Next 10 by 10 as members to the each group.
    As

    For employee prefix is EMP ,Next two digit denotes Group Code and Last digits denotes Employee Number in that Group.


    EMP111
    EMP121
    EMP131
    EMP141...

    For 5 Group Demo:
    Group Code Employee Group Number
    Group 1 LR011
    Group 2 LR012
    Group 3 LR013
    Group 4 LR014
    Group 5 LR015
    Group 1 AT011
    Group 2 AT012
    Group 3 AT013
    Group 4 AT014
    Group 5 AT015
    Group 1 EMP111
    Group 2 EMP121
    Group 3 EMP131
    Group 4 EMP141
    Group 5 EMP151
    Group 1 EMP112
    Group 2 EMP122
    Group 3 EMP132
    Group 4 EMP142
    Group 5 EMP152
    Group 1 EMP113
    Group 2 EMP123

    is it possible to do without VBA (with helper columns/sheet)?

    Share your ideas with VBA/without VBA..

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Excel Formula - Group Assignment

    Attach a sample workbook showing what you expect your output to look like for the sample data.

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Registered User
    Join Date
    01-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Excel Formula - Group Assignment

    Quote Originally Posted by kersplash View Post
    Attach a sample workbook showing what you expect your output to look like for the sample data.

    Go Advanced -> Manage Attachments -> Upload
    Entry Sheet (Yellow fill) feed the Data


    File Attached
    Attached Files Attached Files
    Last edited by deepanc; 03-28-2019 at 01:09 AM.

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Excel Formula - Group Assignment

    In "B1"
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In "B2"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For above "B2" formula create helper column in "Allotment Sheet" Column "E2" is :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Simultaneously press ctrl+SHIFT+ENTER

    For Member per group, required criteria. Hence in allotment sheet group & name join in single cell. [column "G"]
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    "D3" is criteria.
    See attachment.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Excel Formula - Group Assignment

    Please try at Allotment Sheet
    C2
    =IF(A2>'Entry Sheet'!$B$1*'Entry Sheet'!$B$3,"","Group "&MOD(A2-1,'Entry Sheet'!$B$3)+1)
    D2
    =IF(C2="","",LOOKUP(INT((A2-1)/'Entry Sheet'!$B$3),{0,1,2},{"LR0","AT0","EMP"})&MOD(A2-1,'Entry Sheet'!$B$3)+'Entry Sheet'!$B$2&IF(INT((A2-1)/'Entry Sheet'!$B$3)>1,INT((A2-1)/'Entry Sheet'!$B$3)-1,""))
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Excel Formula - Group Assignment

    Quote Originally Posted by Bo_Ry View Post
    Please try at Allotment Sheet
    C2
    =IF(A2>'Entry Sheet'!$B$1*'Entry Sheet'!$B$3,"","Group "&MOD(A2-1,'Entry Sheet'!$B$3)+1)
    D2
    =IF(C2="","",LOOKUP(INT((A2-1)/'Entry Sheet'!$B$3),{0,1,2},{"LR0","AT0","EMP"})&MOD(A2-1,'Entry Sheet'!$B$3)+'Entry Sheet'!$B$2&IF(INT((A2-1)/'Entry Sheet'!$B$3)>1,INT((A2-1)/'Entry Sheet'!$B$3)-1,""))


    Hey..Works Good...

    But I changed a little more in at Allotment Sheet
    C2
    =IF(A2>'Entry Sheet'!$B$1*'Entry Sheet'!$B$3,"","Group "&MOD(A2-1,'Entry Sheet'!$B$3)+1) - because B1 has the number of teams.

    But D2 won't change.. I will try to find the change.. However , Great work

  7. #7
    Registered User
    Join Date
    01-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Excel Formula - Group Assignment

    Hi, dear.. avk I think , I can't make you clear.

    I just want in Allotment Sheet , Column C & D automatically generate (where I fill in manual) based on Entry Sheet Data.

    Sorry, Guys duplicate post..
    Last edited by deepanc; 03-28-2019 at 01:47 PM.

  8. #8
    Registered User
    Join Date
    01-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Excel Formula - Group Assignment

    Quote Originally Posted by avk View Post
    In "B1"
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In "B2"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For above "B2" formula create helper column in "Allotment Sheet" Column "E2" is :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Simultaneously press ctrl+SHIFT+ENTER

    For Member per group, required criteria. Hence in allotment sheet group & name join in single cell. [column "G"]
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    "D3" is criteria.
    See attachment.
    Hi, dear.. avk I think , I can't make you clear.

    I just want in Allotment Sheet , Column C & D automatically generate (where I fill in manual) based on Entry Sheet Data.

  9. #9
    Registered User
    Join Date
    01-18-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Excel Formula - Group Assignment

    Quote Originally Posted by Bo_Ry View Post
    Please try at Allotment Sheet
    C2
    =IF(A2>'Entry Sheet'!$B$1*'Entry Sheet'!$B$3,"","Group "&MOD(A2-1,'Entry Sheet'!$B$3)+1)
    D2
    =IF(C2="","",LOOKUP(INT((A2-1)/'Entry Sheet'!$B$3),{0,1,2},{"LR0","AT0","EMP"})&MOD(A2-1,'Entry Sheet'!$B$3)+'Entry Sheet'!$B$2&IF(INT((A2-1)/'Entry Sheet'!$B$3)>1,INT((A2-1)/'Entry Sheet'!$B$3)-1,""))
    Just I modified the formula and got the data as I want...
    C2
    =IF(A2>'Entry Sheet'!$B$1*'Entry Sheet'!$B$3,"","Group "&MOD(A2-1,'Entry Sheet'!$B$1)+1)

    in D2

    =IF(C2="","",LOOKUP(INT((A2-1)/'Entry Sheet'!$B$1),{0,1,2},{"LR0","AT0","EMP"})&MOD(A2-1,'Entry Sheet'!$B$1)+'Entry Sheet'!$B$2&IF(INT((A2-1)/'Entry Sheet'!$B$1)>1,INT((A2-1)/'Entry Sheet'!$B$1)-1,""))

    Works Good..

+ 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] combine 2 lists with group assignment
    By ElizGreen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-27-2018, 07:34 AM
  2. Roster Assignment (excel formula)
    By Carrol in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2017, 12:09 PM
  3. Replies: 4
    Last Post: 06-26-2015, 02:04 PM
  4. excel formula assignment
    By kanee_2010 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-28-2015, 02:08 AM
  5. [SOLVED] Formula to populate Seating Assignment
    By nicki_rae22 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-28-2014, 10:58 AM
  6. Blocking execution of assignment of formula to Range.formula
    By _JohnyBravo_ in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-09-2013, 11:23 AM
  7. formula to calculate value assignment for each user
    By temba in forum Excel General
    Replies: 2
    Last Post: 06-13-2012, 08:14 AM

Tags for this Thread

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