Forum Discussion
Excel Formula
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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,5)="e.com")),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,8)="test.com")),5),"Agent ""IENT(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 ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,9)="email.com")),5),"Agent ""IENT(SUMPRODUCT(N(RIGHT($C$2:C2,9)="email.com")),5)+1),"")))
- zayn_mirFeb 17, 2023Copper ContributorThank you. If the desired result can not be achieved by a formula, can we do it using some filters?
- OliverScheurichFeb 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 14, 2023Copper ContributorI 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.
- OliverScheurichFeb 14, 2023Gold Contributor
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_mirFeb 13, 2023Copper ContributorSure 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.
- OliverScheurichFeb 12, 2023Gold Contributor
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_mirFeb 10, 2023Copper ContributorThanks 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.
- OliverScheurichFeb 09, 2023Gold Contributor
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_mirFeb 09, 2023Copper ContributorThanks OliverScheurich. Can you share the excel file where you implemented this formula.
- OliverScheurichFeb 08, 2023Gold Contributor
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 ""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,$H$3)=$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,$H$4)=$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),"")))