LinkBack URL
About LinkBacks
I am trying to classify which account belongs to which bucket (see groupings in the same sheet). Any help is appreciated.
I am trying to classify which account belongs to which bucket (see groupings in the same sheet). Any help is appreciated.
Last edited by Annejose20; 04-01-2022 at 09:09 AM.
Welcome to the forum.
Where is your IF statmement? What is the logic?
Please post the workbook again with your expected results for the data sample entered manually. What is obvious to you certainly isn't to me.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
My expected result is -
Account Name Group
ABC Inc Group 2
SOI Ltd Group 2
Where is the updated workbook I asked for?
For those two I'd have expected this:
ABC Inc Group 1
SOI Ltd Group 4
So, once again, what's the logic???
Last edited by AliGW; 04-01-2022 at 09:58 AM.
Not sure what you are asking for ?
I would like TWO things from you, please:
1. An UPDATED workbook with ALL expected results for the small sample dataset inserted.
2. An EXPLANATION of the logic you want to use to assign companies to buckets, because I cannot see how the two examples you have given fit the groups table provided in the current workbook.
Thanks.
Last edited by AliGW; 04-01-2022 at 10:10 AM. Reason: Typo fixed.
I get ABC Inc being Group 2, as it has Bamboo HR and Deel. I don't get why SOI Ltd would not be Group 4, as it only has Connecteam (presuming the ones that say Coonecteam are a typo).
With a list of unique account names starting in F2 (populate using =UNIQUE($A$2:$A$26) for example), I think you want either this:
=IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="Bamboo HR"),1,0))>0,IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="Deel"),1,0))>0,IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="UKG Ready"),1,0))>0,"Group 3","Group 2"),"Group 1"),IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="Connecteam"),1,0))>0,"Group 4","Other"))
or this:
=IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="Bamboo HR"),1,0))>0,IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="Deel"),1,0))>0,IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="UKG Ready"),1,0))>0,"Group 3","Group 2"),IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26<>"Bamboo HR"),1,0))=0,"Group 1","Other")),IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="Connecteam"),1,0))>0,IF(SUM(IF(($A$2:$A$26=F2)*($B$2:$B$26="Connecteam"),1,0))=0,"Group 4","Other")))
The difference between the two is that the second one includes for the ones that say "only", a test that there are no other subscriptions. This causes a number of those to become "Other", as they have e.g. Sage HR as well as Bamboo or Connecteam. The first one effectively ignores anything which is not Bamboo, Deel, UKG Ready, or Connecteam.
As Ali said, this could use some clearer explanation of what you want. Also I expect you want to scale this so hard-coding the names is not ideal. But I'm not going to put in the work to do a more complete solution when I'm not even certain what you need.
Hope this makes sense
I think ABC Inc should be 2 and not 1. Otherwise this gives your expected results:
=IF(SUM(IF((Account!$A$2:$A$26=B2)*(Account!$B$2:$B$26="Bamboo HR"),1,0))>0,IF(SUM(IF((Account!$A$2:$A$26=B2)*(Account!$B$2:$B$26="Deel"),1,0))>0,IF(SUM(IF((Account!$A$2:$A$26=B2)*(Account!$B$2:$B$26="UKG Ready"),1,0))>0,"Group 3","Group 2"),IF(SUM(IF((Account!$A$2:$A$26=B2)*(Account!$B$2:$B$26<>"Bamboo HR"),1,0))=0,"Group 1","N/A")),IF(SUM(IF((Account!$A$2:$A$26=B2)*(Account!$B$2:$B$26="Connecteam"),1,0))>0,IF(SUM(IF((Account!$A$2:$A$26=B2)*(Account!$B$2:$B$26<>"Connecteam"),1,0))=0,"Group 4","N/A")))
I noticed a small error in the version I posted before - the last clause was ="Connecteam" when it should have been <>"Connecteam"
Edit - typo
The logic I wanted to understand was HOW you were deciding on the categories. It looks like Nick has managed to work it out, but if you had explained this right at the start, then you'd have had a solution much sooner.My logic is to use an if statement to assign each account with the groups I have categorised it into
Thanks a lot Nick, Cheers!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Hi to both!
Another option could be:
Check file with the solution. Blessings!PHP Code:=XLOOKUP(TEXTJOIN(", ",,SORT(FILTER(B$2:B$25,A$2:A$25=A2))),N$2:N$5,M$2:M$5,"N/A")
Hi John, thanks for the solution, but it returns everything as N/A
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks