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.

I don't know how your workbook is set up but maybe you can adjust my
assumptions to make this work for you.

Sheet 1
Cell A1 has the title of "row #" (which can be hidden)
Cell A2 contains "=row(B2)-1" and then copied down the column
Cell B1 contains title "record number" (or whatever suits your fancy)
Cells B2:B65536 contain the records (might not be full, but that is where
they are).

Sheet 2
Cell A1 has the title of "row #" (which can be hidden)
Cell A2 contains "=row(B2)-1" and then copied down the column
Cell B1 contains title "People" (or whatever suits your fancy)
Cells B2:B65536 contain the names of people who will be assigned to the
records in cells B2:B65536 (Again might not be full)

Place in Sheet 1 in cell C2 the following formula:
=IF(B2="","",VLOOKUP(ROUNDUP(A2/(COUNTA($B$2:$B$65536)/COUNTA(Sheet2!$B$2:$B$65536)),0),Sheet2!$A$2:$B$65 536,2,FALSE))
Copy this down the column.

Hope that helps.
--
JNW


"Peanut" wrote:

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.

This is perfect - it works beautifully!

Thank you so very much - you just saved me a few boring hours each week!!!

"JNW" wrote:

I don't know how your workbook is set up but maybe you can adjust my
assumptions to make this work for you.

Sheet 1
Cell A1 has the title of "row #" (which can be hidden)
Cell A2 contains "=row(B2)-1" and then copied down the column
Cell B1 contains title "record number" (or whatever suits your fancy)
Cells B2:B65536 contain the records (might not be full, but that is where
they are).

Sheet 2
Cell A1 has the title of "row #" (which can be hidden)
Cell A2 contains "=row(B2)-1" and then copied down the column
Cell B1 contains title "People" (or whatever suits your fancy)
Cells B2:B65536 contain the names of people who will be assigned to the
records in cells B2:B65536 (Again might not be full)

Place in Sheet 1 in cell C2 the following formula:
=IF(B2="","",VLOOKUP(ROUNDUP(A2/(COUNTA($B$2:$B$65536)/COUNTA(Sheet2!$B$2:$B$65536)),0),Sheet2!$A$2:$B$65 536,2,FALSE))
Copy this down the column.

Hope that helps.
--
JNW


"Peanut" wrote:

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.