Forum Discussion
Searching multiple text strings in a range of text strings
Hi all,
I've been working on this problem for a while and hoping all of your expertise will help. I get a list of "User IDs" and "Roles" those users have. I have to figure out which users are covered by other users with the same roles. For example, a user could have 19 roles and cover someone with one role, as long as the user with the 19 roles actually has the role that the user with one role has.
My current strategy has been to make a pivot table to help combine the various roles by user, and then once the roles are combined into one string per user (column G below), I've been trying to search each role of a user until there is a match in column G, once there is a match I would want to bring back the value in column I (can't match on yourself).
I've tried a lot of iterations of Index Match and haven't been able to get it. My new attempt is shown in column E6 but I don't know how to start to craft that so it will look at all the options in G, and lookup column I once a match is found. Does anyone have some thoughts on how I could do this?
The below is a little bit of sample data, the real data could be longer involve 30-50 users. The original data would only be columns A and B, everything else is added.
Thank you, Mark
3 Replies
MP2522 I think I get what you want and I have 3 options for you. Both may have better/more efficient ways but these seem to work.
A) A real easy/quick output showing alternative User options for a given role/row (I'm pretty sure this doesn't meet what you need)
B) I output a coverage grid to show how many 'overlaps' each user has so the diagonal shows the total # roles that user has and any other user on that row with that same # would 'fully cover' that user (I used conditional formatting to highlight them in green). I don't think you wanted a grid like this but I think this is useful as it give a lot of information including total # of roles and alternative 2nd choice User options that have nearly all the same roles
C) This is the more direct answer I think you wanted with just a list of users that 'fully cover' each user
mtarlerThank you very much for the thoughtful reply. I think that gets me very close to where I need to be. In your solution in Columns K-L, is it possible to see who they are covered by who has the most roles? I know my example data is not the best for this because "P111" and "H22" each have 4 roles, but in general, is it possible for count of roles to be involved?
I should have put that in my original post, sorry about that.
