Automating allotment
Hi Team
Please find the attached Query sheet.
I have some 100 documents in column-A of sheet-1.
I have to distribute those equally between say 4 operators as shown in column-B.
Operators' list is placed in sheet-2.
Please let me know if there is any smart way to do this rather than copy-paste each operator name and then duplicating down.
Please suggest a pure excel way without getting into vb scripting.
Thanks & Regards
Kumar.
Please find the attached tested sheet.
Thanks & Regards
Kumar.
| Attachment | Size |
|---|---|
| Query120612.xlsx | 15.03 KB |
Automating allotment with a little of complexity
Kiran say:
I have a similar problem with a bit of complexity.
here is the defnation...
cases are diveded based on complexity level as LOW, MEDIUM and HIGH; also cases are assigned as per complexity level to different operators.
I need a formula that should divide the cases based on complexity level and to the operators designated to handle them.
Please help.
You can download this file with "my solution": https://www.box.com/s/pdlwnvjj8px83iowd9u6
Put the following function
Put the following function into sheet 1 cell c2, copy and paste it down
=OFFSET(Sheet2!$A$1,MOD(ROW($A2),4)+1,0,1,1)
Hi I have tried the above
Hi
I have tried the above suggested OFFSET function but not getting the desired result.
The allotment should be given as shown in column-B -first 25 documents to 1st operator, next 25 documents to 2nd operator and so on.
Please suggest.
As there is no option to attach the tested sheet here, I will be doing that in edit page.
Thanks & Regards
Kumar.
~In your original post, you
~In your original post, you didn't specify that you wanted them in blocks
Put this function in cell D2
Put this function in cell D2 and paste it down
=IF((ROW(E1))<=(COUNTA(A2:A1000))/(COUNTA(Sheet2!A2:A40)),B2,IF((ROW(E1))<=(COUNTA(A2:A1000))/(COUNTA(Sheet2!A2:A40))*2,B2,IF((ROW(E1))<=(COUNTA(A2:A1000))/(COUNTA(Sheet2!A2:A40))*3,B2,IF((ROW(E1))<=(COUNTA(A2:A1000))/(COUNTA(Sheet2!A2:A40))*4,B2,""))))
Hi I have tried this new
Hi
I have tried this new function and found that it is filling the first 4 cells with 1st operator and then giving a message #DIV/0!
The operator names in column-B of sheet-1 are shown for a better understanding of the query. Otherwise, column-B should be empty.
If a function can fill up column-B with the operator names in the desired way rather than doing manually that would be great.
I have attached the tested sheet. Please review and suggest.
Thanks & Regards
Kumar.
The COUNTA(A2:A1000) should
The COUNTA(A2:A1000) should be amended to COUNTA($A$2:$A$1000) throughout the function. You can do that with edit/replace
Hi I have even tried
Hi
I have even tried freezing the cell references in COUNTA(A2:A1000) before I posted my earlier response but the problem still persists.
Moreover I found that the function is taking the help of column-B references. I have filled column-B so you can understand my requirement. Otherwise it should be empty. When I remove the operator names in Column-B, I found that the function in column-D is giving zeros.
Please suggest a function which can fill column-B with operator names placed in sheet-2. Appended is the tested sheet.
Thanks & Regards
Kumar.
Automating...
Try this formula in B2:
=INDIRECT("Sheet2!A"&INT((ROW()-2)/((COUNTA(A:A)-1)/4))+2)
Regards
Jose Maria MURILLO
Hi Jose, This works perfect.
Hi Jose,
This works perfect. however, I have a similar problem with a bit of complexity.
here is the defnation...
cases are diveded based on complexity level as LOW, MEDIUM and HIGH; also cases are assigned as per complexity level to different operators.
I need a formula that should divide the cases based on complexity level and to the operators designated to handle them.
Please help.
Regards,
Kiran