Distribute Agents Equally Based on Team

[Sanjaykar]

Hi,

I have more than 1k records to be assigned to Agents for them to work with the companies. Can someone help me in how what formulas can help me in assigning the Agents of correct teams equally using a macro.
Here is what I want. I want to equally distribute the Accounts in Column A to Agents in Column C and the Agents should belong the the correct teams as in Column B and Column H.

I have attached the excel sheet with very little data.

Example : If there are 10 Companies for Tech team and there are only 5 Agents for Tech Team, then the each Tech agent should be given 2 Companies each.

Can this be done using macros when we have huge data to handle.?

Thanks,
Sanjaykar

[Ochimus]

Sanjaykar,

K3 - M3 in the attached identifies how many accounts there are by category:

=COUNTIF($B:$B,K2)

(Counts how many entries in Col B match K2)

K4 - M4 identifies how many agents there are for each category:

=COUNTIF($H:$H,K2)

(Counts how many entries in Col H match K2)

Problem comes when the number of companies are not an exact "multiple" of the agents? In Col M you have six "Tech" accounts, but only five "tech" agents. So one of them will end up with an extra account.

Ochimus

[Sanjaykar]

Thank you for the help.

Sanjaykar