• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to distribute data(work) in available people

A

atulrajratna

New Member
Hi all, Please help me in this..

If i have a list of 100 files (every file has its own diff number) in 1 tab and another list of 10 people in another tab. Now i want to distribute those 10 people in front of 100 files respectively.

After sorting 10 files to 10 people, for 11th file i again want 1st person's name and so on..

so that on the next day i will change only the list of new files in 1st tab and it will be sorted automatically to those 10 people in another tab.

Thank you!
 
Hi Atul ,


See if the following works :


1. Let us assume your list of 100 files is in Sheet1 , in column A , starting from cell A5.


2. Let us assume your list of 10 people is in Sheet2 , in column A , starting from cell A5.


3. Let us assume you want the 100 files to be distributed , amongst these 10 people in Sheet2 , as follows :


File #1 will appear in cell B5 , file #2 will appear in cell B6 ,....

File #11 will appear in cell C5 , file #12 will appear in cell C6 ,....

File #21 will appear in cell D5 , file #22 will appear in cell D6 ,....

and so on.


To do the above , copy the following formula in the range B5 to K14 :


=INDEX(Sheet1!$A$5:$A$104,(ROW()-ROW($5:$5)+1)+10*(COLUMN()-COLUMN($B:$B)))


Narayan
 
Hi Narayan..

Thanx a lot, it works but quite oppositely.


1. Actually i want name of 10 agents in Sheet1.

and

2. List of 100 files should be in Sheet2

so,

3. I want these 10 agents to be distributed amongst those 100 files.

e.g

File 1 : A

File 2 : B

.

.

File 10 : J

File 11 : A

File 12 : B


Please help me in this,

Thanx again for first one.
 
Hi Atul ,


Sorry for my mistake. Try the following formula :


=INDEX(Sheet1!$A$5:$A$14,MOD(ROW()-ROW($5:$5),10)+1)


Narayan
 
Hi Narayan,

Thanks Thanks Thanks.. IT WORKS!


But..

My BOSS is not happy with this (as usual). B'coz if some day we have only 8 people, or more than 10 people then??


so I modified that formula..

By

-ROW($5:$5),10)+1

To

-ROW($5:$5),Sheet1!$A$1)+1


and in Sheet1!$A$1 I'm entering the number of people available on that day.

its working by entering the number but can we count TEXT by using formula (like we count numbers by using =COUNT)?


Thanks!
 
Hi,


Column A contains list of files, Column B - Names of agents, Column C - status of the file.

Now if

Agent A is working on a file so status of that file will be PENDING. same goes for COMPLETED & CUST REPLY files.

so if I want to count the files which are COMPLETED by agent A & PENDING by agent B. which formula can we use.


table will be like-

_|PEN|COM|REPLY

A | 07 | 02 | 0

B | 04 | 04 | 1

C | 01 | 01 | 3


(A,B,C are the agents)

(This table will be somewhere in the sheet)
 
Hi Atul ,


In the place in your sheet , where you have a table of Agent names in a column , and the status types in the rows across , put in the following formula :


=COUNTIFS($B$5:$B$104,$J5,$C$5:$C$104,K$4)


The addresses here are assuming the following :


1. Your files have already been allocated the agents in a sheet ; the file names , file 1 through file 100 , are in column A , starting from cell A5 ; the agents are listed in column B , from cell B5 through B104.


2. The statuses of all file / agent combinations have been listed in column C , starting from cell C5 through C104.


3. The status types , "Status 1" , "Status 2" and "Status 3" are labelled in cells K4 , L4 and M4.


4. The 10 agents' names are listed in column J , starting from J5 through J14.


5. Put this formula in the cells K5 through M14. Each of the cells will show the count of the files with that agent's name and that status.


Narayan
 
You must log in or register to reply here.
Back
Top