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.
18 Replies
Sort By
- zayn_mirCopper ContributorThanks for the help OliverScheurich. Is there a way that this can be done if we have different domains in my data?
- OliverScheurichGold Contributor
=IF(RIGHT(C2,3)="csv",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="csv")),5)=0,"Agent ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="csv")),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="abc")),5),"Agent ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="abc")),5)+1),""))
You are welcome. I'd apply a nested IF formula for multiple domains.
- zayn_mirCopper ContributorThank you OliverScheurich. Unfortunately both of the formulas are showing some error. Any suggestions?
- OliverScheurichGold Contributor
=IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="uvw")),5)=0,"Agent ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="uvw")),5),"Agent ""IENT(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.