Auto assigning task between 5 people

S

sudhansu121

New Member
Joined
Jan 19, 2021
Messages
28
Office Version
  1. 365
Hello Experts,
Hope you all are safe and doing good.

I need a VBA code or excel formula for the below manual task.

I have a sheet with 1000+ loglines, that gets refreshed every other day. I have 5 members working on the sheet every day and I need to manually allocate records to them which is annoying and also time consuming. (ex 1000 records divided by 5...so 200 each).

In sheet1 I have the data and in Sheet2 I have names of team mates to whom I allocate the records equally, but randomly.

So could anyone please help me here?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
How about this?

SJ_TableToExcel_Pivot (version 2).xlsb
ABCD
1TaskEMPNames
2Task1BillBill
3Task2BillSteve
4Task3BillGreg
5Task4BillTrevor
6Task5BillNick
7Task6Bill
8Task7Bill
9Task8Bill
10Task9Bill
11Task10Bill
12Task11Bill
13Task12Bill
14Task13Bill
15Task14Bill
16Task15Bill
17Task16Bill
18Task17Bill
19Task18Bill
20Task19Bill
Sheet7
Cell Formulas
RangeFormula
A2:A1001A2="Task" & SEQUENCE(1000)
B2:B1001B2=LET(nm,D2:D6,r,RANDARRAY(5),nt,SORT(CHOOSE({1,2},nm,r),2),seq,INT(SEQUENCE(1000,,0)/200)+1,INDEX(nt,seq,1))
Dynamic array formulas.
 
Upvote 0
lrobbo314 said:
How about this?

SJ_TableToExcel_Pivot (version 2).xlsb
ABCD
1TaskEMPNames
2Task1BillBill
3Task2BillSteve
4Task3BillGreg
5Task4BillTrevor
6Task5BillNick
7Task6Bill
8Task7Bill
9Task8Bill
10Task9Bill
11Task10Bill
12Task11Bill
13Task12Bill
14Task13Bill
15Task14Bill
16Task15Bill
17Task16Bill
18Task17Bill
19Task18Bill
20Task19Bill
Sheet7
Cell Formulas
RangeFormula
A2:A1001A2="Task" & SEQUENCE(1000)
B2:B1001B2=LET(nm,D2:D6,r,RANDARRAY(5),nt,SORT(CHOOSE({1,2},nm,r),2),seq,INT(SEQUENCE(1000,,0)/200)+1,INDEX(nt,seq,1))
Dynamic array formulas.
Click to expand...
Thank you so much lrobbo314. I am not sure how to use thi9s formula. I have used now but it takes only 1 persons name :(
 
Upvote 0
sudhansu121 said:
Thank you so much lrobbo314. I am not sure how to use thi9s formula. I have used now but it takes only 1 persons name :(
Click to expand...
Hi Again @lrobbo314,

I have figured out it now :) the only thing is it does not take and divide the total counts automatically and allocate to each one. Like 1000/5 and then allocate to each. But I am calculating and updating these values every time. Is there a way to do this in the formula itslef :)
 
Upvote 0
I don't really follow. Can you post an example of your data and what your desired results would be.

Also, I changed the formula to be more random instead of assigning the tasks in chunks. And I added a pivot table on the side to make sure the counts were 200 a piece.

Book1
ABCDEFG
1TaskEMPNamesEMPCount of Task
2Task1TrevorBillBill200
3Task2SteveSteveGreg200
4Task3TrevorGregNick200
5Task4SteveTrevorSteve200
6Task5GregNickTrevor200
7Task6BillGrand Total1,000
8Task7Greg
9Task8Trevor
10Task9Bill
11Task10Trevor
12Task11Trevor
13Task12Greg
14Task13Trevor
15Task14Steve
16Task15Steve
17Task16Trevor
18Task17Greg
19Task18Bill
20Task19Bill
21Task20Nick
Sheet1
Cell Formulas
RangeFormula
A2:A1001A2="Task" & SEQUENCE(1000)
B2:B1001B2=LET(nm,D2:D6,r,RANDARRAY(5),nt,SORT(CHOOSE({1,2},nm,r),2),seq,INT(SEQUENCE(1000,,0)/200)+1,ch,INDEX(nt,seq,1),rh,RANDARRAY(ROWS(ch)),ct,SORT(CHOOSE({1,2},ch,rh),2),INDEX(ct,,1))
Dynamic array formulas.
 
Upvote 0
lrobbo314 said:
I don't really follow. Can you post an example of your data and what your desired results would be.

Also, I changed the formula to be more random instead of assigning the tasks in chunks. And I added a pivot table on the side to make sure the counts were 200 a piece.

Book1
ABCDEFG
1TaskEMPNamesEMPCount of Task
2Task1TrevorBillBill200
3Task2SteveSteveGreg200
4Task3TrevorGregNick200
5Task4SteveTrevorSteve200
6Task5GregNickTrevor200
7Task6BillGrand Total1,000
8Task7Greg
9Task8Trevor
10Task9Bill
11Task10Trevor
12Task11Trevor
13Task12Greg
14Task13Trevor
15Task14Steve
16Task15Steve
17Task16Trevor
18Task17Greg
19Task18Bill
20Task19Bill
21Task20Nick
Sheet1
Cell Formulas
RangeFormula
A2:A1001A2="Task" & SEQUENCE(1000)
B2:B1001B2=LET(nm,D2:D6,r,RANDARRAY(5),nt,SORT(CHOOSE({1,2},nm,r),2),seq,INT(SEQUENCE(1000,,0)/200)+1,ch,INDEX(nt,seq,1),rh,RANDARRAY(ROWS(ch)),ct,SORT(CHOOSE({1,2},ch,rh),2),INDEX(ct,,1))
Dynamic array formulas.
Click to expand...
Hi @lrobbo314,
Hope you had a great weekend :)
I apologize that I could not reply to you immediately as I was occupied some personal stuffs.

Now coming to your question,

What I want is, for the section where you have taken the counts (INT(SEQUENCE(1000,,0)/200)). So basically you have hardcoded this value (I am not sure). So every time I run the formula, It takes 1000 logline by default.

Ex: If in my sheet I have 200 rows and have to divide among 5 people then it should take only 200 roes and divide among 5 (40/P). So I want the formula to automatically choose how many rows are there in a sheet and then assign among 5 ppl.

But my assumption is, we can not do this using formula, may be wee need a macro to do so.

I hope, have clarified but let me know if you need any more clarification.

SubjectClosed ByAllocation
1500P1P3
1500P2P8
1500P3P6
1500P4P8
1685P5P3
1718P6P3
1718P7P6
1717P8P5
1737P9P4
1737P3
1781P5
1804P6
2079P6
1903P5
1685P3
2008P8
1103P6
2287P8
1249P5
2112P3
2019P3
2018P5
2113P6
2113P4
1344P4
1027P8
1027P4
1027P5
1858P5
2392P5
2328P5
1202P5
2194P8
2392P6
2413P6
2279P6
2278P5
1719P3
2259P3
1783P5
1783P6
1571P4
2253P4
2316P5
2284P6
2284P8
1683P5
1685P4
2336P6
2178P3
1954P6
1960P5
1955P5
1956P5
1402P5
1957P6
1249P3
1249P8
1681P6
1107P6
2322P3
2288P3
2230P4
1619P3
1376P8
2095P4
1224P3
2171P3
1587P8
2562P8
2505P3
1586P6
1240P6
1138P6
1138P8
1412P5
2382P5
2415P3
2686P5
1138P3
1138P5
2422P8
1989P8
1167P5
1138P4
1869P8
1587P5
2665P6
1115P3
2730P6
1816P3
2524P6
2448P5
1669P5
2538P4
2645P8
1390P6
1198P4
2542P5
2761P8
2761P5
2761P3
1501P4
2742P6
2758P6
2325P4
2325P5
2707P5
2754P5
2754P5
2754P4
2754P8
1516P8
2750P6
2750P5
1238P3
1501P8
2742P4
2742P3
2758P5
2325P3
1498P3
1498P4
1498P8
1498P5
2741P8
2741P4
2741P4
2741P4
2764P4
2754P5
2754P3
2754P3
2754P4
2754P3
2586P6
2716P6
2334P6
2334P8
2334P8
2762P4
2662P5
1717P5
2388P3
2493P3
2759P6
1227P5
2337P5
2778P3
2731P3
2780P5
2780P6
2780P3
1011P6
2334P6
1412P6
1412P3
1412P3
1858P4
2662P5
2235P4
2388P5
1827P6
2337P3
2663P6
2779P8
2718P5
2756P6
1590P5
1590P8
1590P6
2501P4
2501P6
2396P3
2740P3
2493P5
2388P5
2280P8
2280P6
2280P5
2552P6
2759P3
2587P5
2781P3
1072P5
2544P6
2485P5
2646P3
2569P8
2646P4
2335P6
2335P4
2335P6
2335P8
2335P3
1353P8
2762P5
1226P6
2765P8
2759P4
2601P4
1438P6
1590P5
2780P6
2780P3
2780P6
1366P6
2546P3
2547P3
1087P3
1087P8
2569P5
2569P8
2569P4
2646P8
1052P5
1052P3
1052P8
1052P4
2801P5
2535P3
1143P6
2474P4
2357P6
2357P6
2801P4
2729P5
2510P8
1817P4
2646P4
2801P8
2801P6
2780P5
2801P6
2357P8
2729P3
2729P3
2729P6
2790P6
2510P6
2652P5
1817P3
1102P3
2646P5
2646P5
2780P5
2801P5
2801P4
2801P6
2801P8
2801P5
2171P3
2578P5
2474P3
2500P8
2729P6
2801P4
2171P8
2171P3
2171P5
2171P3
2535P5
1233P8
2578P3
2790P8
2510P4
2510P6
1249P8
2492P5
2621P3
2621P3
2621P3
2621P5
2621P3
1557P3
P8
P5
P5
P4
P5
P6
P6
P6
P8
P6
P4
P4
P4
P5
P8
P4
P6
P3
P6
P8
P6
P4
P4
P6
P3
P4
P8
P8
P4
P3
P8
P4
P5
P4
P6
P3
P6
P6
P3
P5
P6
P8
P4
P4
P3
P4
P6
P3
P5
P4
P3
P4
P8
P6
P4
P3
P4
P5
P3
P5
P6
P5
P4
P3
P4
P3
P3
P8
P6
P8
P4
P6
P3
P6
P5
P6
P4
P4
P4
P4
P4
P6
P4
P4
P4
P4
P3
P4
P4
P8
P4
P4
P4
P5
P5
P3
P3
P6
P5
P6
P4
P6
P3
P5
P8
P8
P5
P4
P8
P6
P5
P5
P4
P6
P3
P8
P5
P8
P4
P4
P5
P5
P5
P3
P8
P3
P3
P3
P6
P6
P4
P5
P5
P4
P6
P3
P4
P5
P8
P5
P6
P6
P3
P8
P8
P3
P5
P8
P6
P5
P6
P4
P4
P4
P6
P8
P8
P4
P8
P5
P8
P5
P5
P3
P3
P5
P4
P8
P6
P6
P5
P8
P8
P8
P6
P4
P6
P5
P4
P4
P8
P6
P4
P5
P8
P3
P5
P8
P5
P6
P8
P6
P5
P6
P4
P4
P8
P8
P4
P5
P3
P6
P3
P8
P4
P8
P3
P8
P4
P5
P3
P3
P8
P3
P3
P3
P8
P8
P8
P3
P8
P4
P5
P8
P3
P6
P8
P3
P8
P4
P5
P8
P5
P5
P8
P6
P8
P8
P5
P4
P3
P4
P8
P4
P3
P5
P8
P3
P6
P8
P5
P6
P5
P6
P3
P8
P4
P3
P5
P5
P4
P4
P5
P3
P6
P5
P8
P5
P8
P3
P6
P8
P5
P6
P4
P5
P8
P8
P4
P5
P4
P5
P6
P8
P6
P5
P6
P5
P3
P5
P3
P8
P6
P8
P3
P8
P8
P3
P6
P3
P5
P5
P5
P3
P5
P3
P4
P5
P8
P3
P8
P4
P8
P6
P4
P6
P6
P3
P4
P6
P4
P4
P3
P4
P6
P8
P5
P8
P3
P3
P8
P3
P3
P5
P6
P3
P3
P4
P4
P5
P4
P4
P8
P6
P5
P4
P4
P3
P8
P3
P4
P6
P3
P8
P3
P5
P3
P8
P4
P4
P6
P3
P4
P6
P6
P3
P6
P3
P8
P8
P6
P3
P5
P3
P8
P4
P4
P5
P4
P5
P6
P5
P4
P4
P4
P3
P8
P3
P8
P8
P5
P5
P5
P6
P8
P8
P6
P5
P4
P3
P6
P3
P3
P8
P8
P4
P5
P6
P3
P5
P6
P3
P5
P6
P4
P8
P8
P8
P4
P4
P3
P8
P8
P4
P8
P4
P3
P5
P5
P8
P6
P4
P4
P5
P6
P6
P5
P3
P5
P8
P4
P5
P8
P8
P8
P6
P4
P8
P4
P4
P3
P4
P3
P6
P8
P3
P5
P8
P6
P8
P3
P8
P3
P4
P6
P4
P8
P4
P5
P3
P8
P6
P4
P3
P4
P8
P3
P8
P5
P3
P4
P6
P5
P4
P4
P8
P4
P5
P3
P3
P8
P6
P5
P8
P8
P5
P3
P5
P5
P3
P6
P3
P5
P5
P8
P4
P4
P6
P6
P6
P8
P8
P5
P4
P6
P3
P4
P8
P6
P3
P3
P3
P6
P5
P3
P6
P3
P8
P6
P3
P6
P3
P4
P8
P6
P6
P4
P6
P4
P5
P8
P3
P4
P5
P8
P8
P8
P8
P4
P6
P6
P8
P6
P3
P8
P6
P5
P6
P4
P4
P3
P8
P3
P6
P8
P4
P8
P5
P6
P4
P6
P6
P3
P6
P4
P6
P6
P8
P4
P6
P6
P8
P6
P6
P4
P6
P3
P3
P5
P8
P6
P6
P5
P5
P8
P5
P3
P5
P5
P6
P3
P6
P5
P6
P4
P4
P4
P5
P5
P5
P6
P6
P4
P6
P3
P6
P8
P3
P8
P8
P8
P6
P3
P4
P5
P6
P4
P4
P8
P5
P4
P3
P8
P4
P3
P5
P5
P8
P8
P8
P4
P4
P6
P4
P4
P5
P6
P5
P4
P3
P3
P8
P6
P3
P5
P5
P5
P8
P6
P4
P3
P3
P8
P6
P3
P4
P3
P6
P4
P8
P6
P8
P4
P4
P8
P8
P8
P3
P3
P8
P8
P6
P8
P3
P3
P5
P3
P5
P8
P5
P6
P4
P4
P6
P5
P4
P4
P8
P4
P5
P4
P8
P5
P6
P4
P5
P8
P3
P6
P6
P4
P3
P4
P4
P3
P3
P4
P4
P5
P6
P8
P3
P8
P3
P8
 
Upvote 0
This version is more dynamic. The numbers aren't hard coded and it uses named ranges so that it will update automatically.

Book1
ABCDEFGH
1TaskEMPNamesEMPCount of Task
2Task1GregBillBill8
3Task2NickSteveGreg8
4Task3BillGregNick8
5Task4SteveTrevorSteve8
6Task5SteveNickTrevor8
7Task6TrevorGrand Total40
8Task7Trevor
9Task8Bill
10Task9Nick
11Task10Bill
12Task11Bill
13Task12Trevor
14Task13Steve
15Task14Steve
16Task15Greg
17Task16Trevor
18Task17Greg
19Task18Greg
20Task19Steve
21Task20Nick
22Task21Greg
23Task22Greg
24Task23Steve
25Task24Trevor
26Task25Bill
27Task26Bill
28Task27Nick
29Task28Trevor
30Task29Steve
31Task30Trevor
32Task31Greg
33Task32Greg
34Task33Bill
35Task34Nick
36Task35Nick
37Task36Bill
38Task37Trevor
39Task38Steve
40Task39Nick
41Task40Nick
Sheet1
Cell Formulas
RangeFormula
A2:A41A2="Task" & SEQUENCE(40)
B2:B41B2=LET(nm,Names,ro,ROWS(Tasks),nl,COUNTA(nm),r,RANDARRAY(nl),nt,SORT(CHOOSE({1,2},nm,r),2),seq,INT(SEQUENCE(ro,,0)/(ro/nl))+1,ch,INDEX(nt,seq,1),rh,RANDARRAY(ROWS(ch)),ct,SORT(CHOOSE({1,2},ch,rh),2),INDEX(ct,,1))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Names=OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D)-1)B2
Tasks=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)B2
 
Upvote 0
Solution
lrobbo314 said:
This version is more dynamic. The numbers aren't hard coded and it uses named ranges so that it will update automatically.

Book1
ABCDEFGH
1TaskEMPNamesEMPCount of Task
2Task1GregBillBill8
3Task2NickSteveGreg8
4Task3BillGregNick8
5Task4SteveTrevorSteve8
6Task5SteveNickTrevor8
7Task6TrevorGrand Total40
8Task7Trevor
9Task8Bill
10Task9Nick
11Task10Bill
12Task11Bill
13Task12Trevor
14Task13Steve
15Task14Steve
16Task15Greg
17Task16Trevor
18Task17Greg
19Task18Greg
20Task19Steve
21Task20Nick
22Task21Greg
23Task22Greg
24Task23Steve
25Task24Trevor
26Task25Bill
27Task26Bill
28Task27Nick
29Task28Trevor
30Task29Steve
31Task30Trevor
32Task31Greg
33Task32Greg
34Task33Bill
35Task34Nick
36Task35Nick
37Task36Bill
38Task37Trevor
39Task38Steve
40Task39Nick
41Task40Nick
Sheet1
Cell Formulas
RangeFormula
A2:A41A2="Task" & SEQUENCE(40)
B2:B41B2=LET(nm,Names,ro,ROWS(Tasks),nl,COUNTA(nm),r,RANDARRAY(nl),nt,SORT(CHOOSE({1,2},nm,r),2),seq,INT(SEQUENCE(ro,,0)/(ro/nl))+1,ch,INDEX(nt,seq,1),rh,RANDARRAY(ROWS(ch)),ct,SORT(CHOOSE({1,2},ch,rh),2),INDEX(ct,,1))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Names=OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D)-1)B2
Tasks=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)B2
Click to expand...
Thanks man for your support so far. Is there anything I must changes in this formula? Like changing the column reference or something ? Because. it shows me #NAME? error :(.
 
Upvote 0
I would imagine it's because you didn't set up the named ranges from the bottom portion of my last post.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Auto assign tasks
Replies
2
Views
115
Anthony86
A
C
  • Question
Repetitive ”randomly” assigning/selecting team members for a task based on previous number of selections/assignments.
Replies
0
Views
337
ciprinitu
C
P
  • Solved
How to keep a running 5 cell total
2 3
Replies
21
Views
661
dreid1011
dreid1011
seanjon
  • Solved
Multiple Criteria VBA with Action
Replies
2
Views
330
seanjon
seanjon
J
  • Solved
Assign a value to a range of cells based on the color of conditional formatting
Replies
3
Views
754
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,214,661
Messages
6,120,790
Members
448,994
Latest member
rohitsomani

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top