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
Feb 01, 2023
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),"")))))

         

Resources