Excel Formula

[zayn_mir]

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.

[#2]

@zayn_mir 

=IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="uvw")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="uvw")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="uvw")),5)+1)

You can try this formula if there is only one domain ("uvw" in the example) in your data.

 

[#3]

Thanks for the help @OliverScheurich. Is there a way that this can be done if we have different domains in my data?

[#4]

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

[#5]

Thank you @OliverScheurich. Unfortunately both of the formulas are showing some error. Any suggestions?

[#6]

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

[#7]

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)

[#8]

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

[#9]

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?

[#10]

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

[#11]

Thanks @OliverScheurich. Can you share the excel file where you implemented this formula.

[#12]

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

[#13]

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.

[#14]

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

[#15]

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.

[#16]

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

[#17]

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.

[#18]

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

[#19]

Thank you. If the desired result can not be achieved by a formula, can we do it using some filters?