Hi guys,
This is a continuation of the following topic. (https://www.mrexcel.com/forum/excel-questions/1029670-grouping-data.html) In hindsight I could have probably explained my challenge a little better and this is my attempt in doing so. For my work we have a system where we have multiple users within groups called subscriptions. I've created an Excel to showcase my problem here: https://www.dropbox.com/s/s4c0tilwhqtbtk6/Example 2.0.xlsx?dl=0
The Goal
To divide the users into groups with a subscriptions. I have multiple sheets with information about the user (sign permission per account, account rights, companies he has access to and more) and if all the data of two users within a subscription is the same I can group them. If not the user will be in a different group.
The challenge
Normally I would do this in Excel, but I have to do this grouping multiple times and doing this in script will save me a lot of time. Another challenge is that some users have access to more than 3000 accounts, thus you can’t concatenate all in one cell since it would be more than 32.000 characters.
I’ve made an example Excel with two tabs with the data (Current xx). My end result is in the Overview tab. The first subscription has 2 users with the same info in both sheets so they go to the same group. The last subscription has 4 users with different permissions so they go in different groups.
The way I currently try to solve the problem is:
But I think and hope that you can help me with a quicker and more efficient solution. Any help would be greatly appreciated.
Kind regards,
Ricardo
This is a continuation of the following topic. (https://www.mrexcel.com/forum/excel-questions/1029670-grouping-data.html) In hindsight I could have probably explained my challenge a little better and this is my attempt in doing so. For my work we have a system where we have multiple users within groups called subscriptions. I've created an Excel to showcase my problem here: https://www.dropbox.com/s/s4c0tilwhqtbtk6/Example 2.0.xlsx?dl=0
The Goal
To divide the users into groups with a subscriptions. I have multiple sheets with information about the user (sign permission per account, account rights, companies he has access to and more) and if all the data of two users within a subscription is the same I can group them. If not the user will be in a different group.
The challenge
Normally I would do this in Excel, but I have to do this grouping multiple times and doing this in script will save me a lot of time. Another challenge is that some users have access to more than 3000 accounts, thus you can’t concatenate all in one cell since it would be more than 32.000 characters.
I’ve made an example Excel with two tabs with the data (Current xx). My end result is in the Overview tab. The first subscription has 2 users with the same info in both sheets so they go to the same group. The last subscription has 4 users with different permissions so they go in different groups.
The way I currently try to solve the problem is:
- Insert a row after B
- Concatenate the user info per row into one cell in row C
- Insert a row after C
- Combine the concatenate each row if the row above is from the same user in row D (=if(a1&b1=a2&b2;D1&”,”&C2;C2))
- Insert a row after D
- Check length of row D (=LEN(D2)) in row E
- Sort row E high to low
- Remove duplicates based on column A &B
- Do this for all sheets
- Vlookup all the results in all sheets into the overview
- Compare them and if the same group them
But I think and hope that you can help me with a quicker and more efficient solution. Any help would be greatly appreciated.
Kind regards,
Ricardo
)
