permissions excel multi option filter

I have a spreadsheet of security permissions for one of our ERP applications. Of specific interest is a column B named "role", and column G "username". Each username can be assigned 1 or more security roles in our ERP application. What I need to do, is for any instances whereby users have a role called "read only", is the list any other roles they have also been assigned, (which they shouldn't have any, but there may be instances where they have been assigned read-only and then other roles too!). can anyone advise how this could be achieved. It is a huge report so doing it manually is not practical.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:

I have selected another column [ I ], & added this formula to cell [I2]:
=IF(AND(B2<>"read only",COUNTIFS(G:G,G2,B:B,"read only")>0),G2,"")

(Any other column outside of your current used range will work just as well, though)

I then copied [I2] down the same column to the extent of the data.

Please see the attached workbook.  Note that the data in columns [ B ] & [G] are based on random number generation.  Re-calculating the worksheet will, hence, change the data shown (so the results in column [ I ] will be different).

Also note that the formula does not remove duplicates from column [ I ].

Will this be a requirement, or can you handle this yourself?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

in a helper's column you could use

=SIGN(COUNTIFS(B:B,"read only",G:G,G5)*COUNTIFS(B:B,"<>read only",G:G,G5))

and filter on non 0 results for the ones you shouldn't be

As another option, you could create a pivot table with User field and Role field, and a count of roles.  You should be able to see quickly any user who has more than one role, and which ones have "read only" and another.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.