# Excel Formula

Hi I need help,

I need the emails of different people with the same domain to be assigned to 1 Agent. If there are more than 5 people ( I want the next 5 ) to be assigned to a different Agent.

18 Replies

# Re: Excel Formula

``=IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="uvw")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="uvw")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="uvw")),5)+1)``

You can try this formula if there is only one domain ("uvw" in the example) in your data. # Re: Excel Formula

Thanks for the help @OliverScheurich. Is there a way that this can be done if we have different domains in my data?

# Re: Excel Formula

``=IF(RIGHT(C2,3)="csv",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5)+1),IF(RIGHT(C2,3)="abc",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5)+1),""))``

You are welcome. I'd apply a nested IF formula for multiple domains. # Re: Excel Formula

Thank you @OliverScheurich. Unfortunately both of the formulas are showing some error. Any suggestions?

# Re: Excel Formula

``=IF(RIGHT(C2,3)="csv",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5)+1),IF(RIGHT(C2,3)="abc",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5)+1),IF(RIGHT(C2,3)="usa",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="usa")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="usa")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="usa")),5)+1),IF(RIGHT(C2,3)="zzz",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="zzz")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="zzz")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="zzz")),5)+1),IF(RIGHT(C2,3)="edv",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="edv")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="edv")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="edv")),5)+1),"")))))``

Both formulas work flawlessly and as expected in my sheet. I've expanded the nested IF formula for 5 domains and it returns the intended results. Below i've highlighted the initial criteria of each IF statement for explanation.

=IF(RIGHT(C2,3)="csv",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="csv")),5)+1),

IF(RIGHT(C2,3)="abc",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="abc")),5)+1),

IF(RIGHT(C2,3)="usa",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="usa")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="usa")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="usa")),5)+1),

IF(RIGHT(C2,3)="zzz",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="zzz")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="zzz")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="zzz")),5)+1),

IF(RIGHT(C2,3)="edv",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="edv")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="edv")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,3)="edv")),5)+1),""))))) # Re: Excel Formula

Thanks a lot @OliverScheurich. I really appreciate your help. One quick question, can we use the formula provided by you for the whole domain name (etc; email.com, e.com, test.com)? As of now the formula is just working for the domain that comes after (.) (etc; com, uvw, abc)

# Re: Excel Formula

You are welcome. This formula returns the intended result in my sheet.

=IF(RIGHT(C2,5)="e.com",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,5)="e.com")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,5)="e.com")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,5)="e.com")),5)+1),

IF(RIGHT(C2,8)="test.com",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,8)="test.com")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,8)="test.com")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,8)="test.com")),5)+1),

IF(RIGHT(C2,9)="email.com",IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,9)="email.com")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,9)="email.com")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,9)="email.com")),5)+1),"")))

# Re: Excel Formula

That worked well @OliverScheurich. Do I have to enter a loop in the formula for every domain in my sheet or is there a way that the domains become a variable and then the variable is added to the formula you sent?

# Re: Excel Formula

With this formula it's possible to enter variable domains in cells G2, G3 and G4. In H2 is the formula =LEN(G2) which is copied down.

=IF(RIGHT(C2,\$H\$2)=\$G\$2,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5)+1),

IF(RIGHT(C2,\$H\$3)=\$G\$3,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5)+1),

IF(RIGHT(C2,\$H\$4)=\$G\$4,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)+1),"")))

# Re: Excel Formula

Thanks @OliverScheurich. Can you share the excel file where you implemented this formula.

# Re: Excel Formula

Yes, here it is. I've updated the formula in case there are domains such as "teste.com" and "e.com". In these domains the last 5 digits are identical ( e.com ) and this is considered by the new updated formula.

# Re: Excel Formula

Thanks @OliverScheurich let me check it out on my sheet. I have a question regarding another Excel automation. Can I get a formula through which Excel finds specific data (eg TL6, TL7) from all of the data in a cell and just copy this data (eg TL6,TL7) on a new cell.

# Re: Excel Formula

You are welcome. I'm not sure what you exactly want to do with specific data e.g. TL6, TL7 from a cell. Perhaps you can start a new discussion and give an example of your data and the expected result both without sensitive information.

# Re: Excel Formula

Sure I will start a new discussion regarding the other query. Talking about the existing Formula isn't there a formula through which I can add unlimited domains in column G and the Agents get assigned to the domains in column C.

# Re: Excel Formula

I don't know if there is a formula for an unlimited number of domains. With a nested IF you can reference 64 domains and if you work with Excel 2019 or later you can apply a nested IFS which can reference up to 127 domains.

# Re: Excel Formula

I am implementing the formulas you provided on Google Sheets so can you provide me with the formula through which I can add maximum number of domains.

# Re: Excel Formula

If there are actually e.g. 100 domains in your sheet i'd count 10 domains at a time and save the results after each step.

In order to expand the formula you can copy the highlighted part of the formula:

=IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$2,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5)+1),

IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$3,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5)+1),

IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$4,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)+1),"")))

Then paste the copied part of the formula into the last part of the formula which is highlighted in green:

=IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$2,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5)+1),

IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$3,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5)+1),

IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$4,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)+1),"")))

In the last step replace \$H\$4 and \$G\$4 with \$H\$5 and \$G\$5 within the recently pasted part of the formula.

=IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$2,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$2)=\$G\$2)),5)+1),

IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$3,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$3)=\$G\$3)),5)+1),

IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$4,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)+1),

IF(RIGHT(C2,LEN(C2)-FIND("@",C2))=\$G\$4,IF(MOD(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT(\$C\$2:C2,\$H\$4)=\$G\$4)),5)+1),""))))

This can be repeated for up to 64 nestings with the IF formula.

# Re: Excel Formula

Thank you. If the desired result can not be achieved by a formula, can we do it using some filters?