Forum Discussion
zayn_mir
Jan 27, 2023Copper Contributor
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.
zayn_mir
Feb 14, 2023Copper Contributor
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
Feb 14, 2023Gold Contributor
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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$2)=$G$2)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$3)=$G$3)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$4)=$G$4)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$2)=$G$2)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$3)=$G$3)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$4)=$G$4)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$2)=$G$2)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$3)=$G$3)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$4)=$G$4)),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,$H$4)=$G$4)),5),"Agent ""IENT(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_mirFeb 17, 2023Copper ContributorThank you. If the desired result can not be achieved by a formula, can we do it using some filters?