Count, divide and assign

[Luci Packett, ACC]

Good day. New to posting but I review the newsgroup all the time. Great stuff. I have a client who buys lists. We need to be able to count the number of contacts on the list, divide between the number of sales people and then assign that number of contacts to each salesperson.

So for example if the spreadsheet has 200 contacts, we need for a macro, etc. to divide into the number of sales people, lets say 5 (maybe have the sales people listed on another worksheet?) and assign that number of (40) contacts to each sales person by putting their name in the sales rep field. I am fairly certain this can be done but I just don't know the best way to get it started.

Thanks in advance for any help you may offer.


Luci M. Packett
ACT! Certified Consultant
Stewart Technologies, Inc.

 

[CLR]

Just copy the list of Sales People, and paste it into a helper column at the
right side of the contacts list........if you paste it to the COLUMN, not the
cells, it should replicate itself down the contacts list...........then just
sort by this column.

hth
Vaya con Dios,
Chuck, CABGx3

 

[Pete_UK]

If you want to do it by means of a formula, then put your list of
Sales Reps in a new sheet (named "Reps") from A1 downwards, and then
in your Sales_rep field you can put this formula:

=INDIRECT("Reps!A"&MOD(ROW(A1),COUNTA(Reps!A$1:A$20))+1)

This will count the number of sales reps you have in column A of the
Reps sheet (max 20, but you can change this quite easily if you have
more) and then allocate them in sequence down the Sales_rep field as
you copy the formula down.

Hope this helps.

Pete

 

[Luci Packett, ACC]

Thank you both for great information!

--
Luci M. Packett
Stewart Technologies, Inc.

Good day. New to posting but I review the newsgroup all the time. Great stuff. I have a client who buys lists. We need to be able to count the number of contacts on the list, divide between the number of sales people and then assign that number of contacts to each salesperson.

So for example if the spreadsheet has 200 contacts, we need for a macro, etc. to divide into the number of sales people, lets say 5 (maybe have the sales people listed on another worksheet?) and assign that number of (40) contacts to each sales person by putting their name in the sales rep field. I am fairly certain this can be done but I just don't know the best way to get it started.

Thanks in advance for any help you may offer.


Luci M. Packett
ACT! Certified Consultant
Stewart Technologies, Inc.

 

[Luci Packett, ACC]

Pete,

One list is very basic and the original formula will work fine. Thanks so
much.

Now the million dollar question. Is there a formula that can do the
following with conditions? Meaning if the contact contains AAMCO assign to
Bill? For instance Joe gets Meineke and Bill get AAMCO but all the sales
reps still need to get the same number of contacts. This person is spending
an hour or so assigning reps to their "concrete" accounts. Then assigning
left overs too. There has got to be an easier way.

For example:

200 contacts on a list
5 reps
Bill get AAMCO, on the list there are 10 AAMCO's, so Bill needs 30 more
misc. records.
Joe gets Meineke, on the list there are 5 Meineke's, so Joe needs 35 misc.
more records
The other 3 sales reps still need 40 contacts each.

Does that make sense? Can it be done? If not at least one list will be easy
to bang out and import into the database. Thanks so much for your wisdom.

--
Luci M. Packett
Stewart Technologies, Inc.

If you want to do it by means of a formula, then put your list of
Sales Reps in a new sheet (named "Reps") from A1 downwards, and then
in your Sales_rep field you can put this formula:

=INDIRECT("Reps!A"&MOD(ROW(A1),COUNTA(Reps!A$1:A$20))+1)

This will count the number of sales reps you have in column A of the
Reps sheet (max 20, but you can change this quite easily if you have
more) and then allocate them in sequence down the Sales_rep field as
you copy the formula down.

Hope this helps.

Pete

 

[Luci Packett, ACC]

Anyone have a formula for this one?

Now the million dollar question. Is there a formula that can do the
following with conditions? Meaning if the contact contains AAMCO assign
to Bill? For instance Joe gets Meineke and Bill get AAMCO but all the
sales reps still need to get the same number of contacts. This person is
spending an hour or so assigning reps to their "concrete" accounts. Then
assigning left overs too. There has got to be an easier way.

For example:

200 contacts on a list
5 reps
Bill get AAMCO, on the list there are 10 AAMCO's, so Bill needs 30 more
misc. records.
Joe gets Meineke, on the list there are 5 Meineke's, so Joe needs 35 misc.
more records
The other 3 sales reps still need 40 contacts each.

Does that make sense? Can it be done? If not at least one list will be
easy to bang out and import into the database. Thanks so much for your
wisdom.

--
Luci M. Packett
Stewart Technologies, Inc.

If you want to do it by means of a formula, then put your list of
Sales Reps in a new sheet (named "Reps") from A1 downwards, and then
in your Sales_rep field you can put this formula:

=INDIRECT("Reps!A"&MOD(ROW(A1),COUNTA(Reps!A$1:A$20))+1)

This will count the number of sales reps you have in column A of the
Reps sheet (max 20, but you can change this quite easily if you have
more) and then allocate them in sequence down the Sales_rep field as
you copy the formula down.

Hope this helps.

Pete