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
OliverScheurich's avatar
OliverScheurich
Gold Contributor

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.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 09, 2023

    zayn_mir 

    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.

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

    zayn_mir 

    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.

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

    zayn_mir 

    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.

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

    zayn_mir 

    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.

  • zayn_mir's avatar
    zayn_mir
    Copper Contributor
    Feb 17, 2023
    Thank you. If the desired result can not be achieved by a formula, can we do it using some filters?

Share

Resources