Excel Formula

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 

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 

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 

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.