Forum Discussion

zayn_mir's avatar
zayn_mir
Copper Contributor
Jan 27, 2023

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.
Formulas and Functions
zayn_mir's avatar
zayn_mir
Copper Contributor
Thanks for the help OliverScheurich. Is there a way that this can be done if we have different domains in my data?
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 01, 2023

zayn_mir 

=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.

 

  • zayn_mir's avatar
    zayn_mir
    Copper Contributor
    Feb 07, 2023
    Thank you OliverScheurich. Unfortunately both of the formulas are showing some error. Any suggestions?
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Feb 07, 2023

      zayn_mir 

      =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),"")))))

       

      • zayn_mir's avatar
        zayn_mir
        Copper Contributor
        Feb 08, 2023
        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)

Resources