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 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)
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 08, 2023

zayn_mir 

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

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

      zayn_mir 

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

      • zayn_mir's avatar
        zayn_mir
        Copper Contributor
        Feb 09, 2023
        Thanks OliverScheurich. Can you share the excel file where you implemented this formula.

Resources