Dividing up Records between team members

P

Peanut

I have 10,000 records that need to be evenly divided between 40 people. The
quantity of records and # of people change weekly. Presently I am manually
dividing the # of records by the # of people to determine # of records each,
and then copy/pasting each name down 250 or so rows. I have searched
throughout the help section to see if there's a formula I could write that
would do this (these) task(s) for me, but I'm not having any luck. I was
hoping that someone might have a suggestion as the manual process is becoming
very tedious. Any help would be GREATLY appreciated.
 
S

Seahawk60B

Assuming that it doesn't matter in what order the records are divided
up, you could create a macro that would loop through all the records,
and begin assigning record 1 to person 1, record 2 to person 2, record
3 to person 3, and so on, until you got to the last person, then start
over with person 1 at the next record. That would be the most evenly
divided method, and probably the easiest to program, although it would
require a little bit of coding skill.

If that's over your head, you're welcome to send me your spreadsheet,
and I should be able to do it without too much difficulty
 
B

Bill Kuunders

Assuming column A has the records
Column B has the names

Enter the 4 names in column B
First four cells only and extend this series down the column as far as the
records in A
I.e. Every fourth cell will have the same name.
and therefore they will always have the same number of records.

Select column B only and go to <>data<> sort to sort it.
Do not expand the selection .
Done....

Greetings from NZ
Bill K
 
H

hot dogs

I think i have achieved what you want.

i have a list of random numbers in cell A5 downwards, i have assumed you may
have cells above the list for titles, etc. In column F I have a list of names
(i have just used a,b,c,d, etc). In cell E1 i have =if(F1="","",1), this
gives me a number 1 for the first name, in cell E2 i have =if(F2="","",E1+1).
Copy this formula down to what you think is the maximum number of peoples
names you will have.
In cell G1 i have entered =count(A5:A44), instead of A44 enter the last cell
of your maximum record list length (it might be A10000).
In cell G2 i have =count(E1:E10), instead of E10 enter the last cell of your
maximum people list length (it might be E40).
In cell G3 i have =G1/G2

In the cell to the right of your record list (in the top cell) i have
=if(A5="","",(lookup(roundup((row(a5)-4)/$g$3,0)0$e$1:$e$10,$f$1:$f$10)))
copy this formula down the length of the list (or the length list at its
maximum length).

now as you enter values in the record list or names to the list of names the
list of names are written next to the record list with, (for example if there
were 20 records and 5 names) a next to 1-5, b next to 6-10, c next to 11-15,
etc. if you set up what i have described you will beable to see what is
happening and apply it to you sheet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top