Formula that check and give me results like "green(ok", "red(not ok)"

[shinecomo]

Hello all!
i need your pro help

I need to have a formula that check and give me results like "green(ok", "red(not ok)".
Let me explain:

I have a report with columns like username, name, surname, office, groups.
Every user has a lot of raw because every raw give me a particolar group where the user is. So for example:

USERNAME NAME SURNAME OFFICE GROUP
123456 jon doe nyc lawyer
123456 jon doe nyc legal
123456 jon doe nyc smartwork
123456 jon doe nyc keys
etc etc etc

Well, i need to check for every username(so mutiples raws for each one) if the user has a list of "groups". If yes is also possible to color the username(each row) with green or write somewhere something like "pass"?

Thanks to all

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[etaf]

EDIT - ignore - Moderator posted as i was typing

can you give an example
you should be able to use a countif() or countifs()
but i dont understand what meets a green , having more than 1 group - or must only be in 1 group ?
so what colour should your example be

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A sample sheet would help here


Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[shinecomo]

Hello all, really sorry for not reading the forum rules. Thanks for your help.

Attached you can find and example of what i need.

sheet "user" contains an extraction of users with their username and groups(distribution list where they are currently)
In sheet "job" you can find the job position and the group that this type of user have to have.

I would like to find a formula that check for every job position the entire "users" sheet, finding for every user the position based on wich groups they have. If, like the example, user Jon Doe have all the "prince" group, the formula should write something like "OK PRINCE" for him.
The case where a user don't have all the groups for a particular position (but have some of them), the formula have to report it writing something like "not ok + the possible job position name (based on the group that it finds).

I hope to have explained in a good way what i need. excuse me if not but im not english

Thanks a lot

[AliGW]

I don't understand - sorry. Please explain the text in the red and green sections - what exactly needs to be checked and why? Are the lookup lists incomplete?

[shinecomo]

Hello Ali
the green and red cells are the results that i wish to get.
I need that, for every user, excel check their groups, and compare them with the groups in the "jobs" sheet.
Based on what is the result of this comparison (user groups + jobs group lists), excel have to write near every user, if they have all the groups that a job position should have.
Like in the example, the job position "PRINCE" has to have "dell, lenovo, ios". The user "jon doe" meet this requisites (because he have these groups, so excel writes "ok + the name of the job position corresponding)
In the "jack russell" user example, excel couldnt find all the groups for one of the jobs position, so he writes "not ok + name of the best matching job position based on the user's groups found"

[etaf]

you user 2494144 mila brown
is NOT OK for King, But your not showing that

A little confused with the When its OK and when NOT OK
or the priority - is prince more important queen in the list

In the "jack russell" user example, excel couldn't find all the groups for one of the jobs position, so he writes "not ok + name of the best matching job position based on the user's groups found"

Jack is also OK for PRINCE , NOT just Queen as your example show - so dont know how we prioritise BEST MATCHING in your example

The user "jon doe" meet this requisites (because he have these groups, so excel writes "ok + the name of the job position corresponding)

again, I dont know why Jon doe, has OK for Prince, when he/she also OK for Queen - nothing to say what job you are trying to match


you could have 3 columns , 1 for Prince 1 for queen one for king - so you can see if they are OK for what skill
they could be hidden and setup as helper columns and then your main column have OK KING,QUEEN - NOT OK PRINCE - type info

anyway - heres an example with the three columns , one for each job and test to see if that matches the user skill set

just to give you some idea, the formula is a draft , and could be improved and simplified, but i dont have the time to do that for something that may not be what you need anyway
so i we used to say - a strawman idea

[shinecomo]

Hello all and thanks again.
ETAF i've tried to modify your formula but it doesnt work.
So i re-made an excel file to explain me better.
User sheet: contain the list of users with associated groups ownerships.
Job sheet: contains the list of the jobs. Every job have multiple "groups of ownership".
I'd like that the formula check the groups of every user, and report on the "users sheet" if a user is a counsel, a trainee or something else (basing this decision on the groups where each user belong).
If for example a user result to be a "counsel", but is missing other "required groups" (see every job). The formula should also write wich are the required missing group.

Hope that my example is clearer and i thank you a lot guys

[etaf]

i'm not following the example exactly - however, i'm now away for a few days , so will look again on my return next week.

[etaf]

no i'm sorry , really not following now

My understanding was that all the JOBS had to match

you have in STAGISTA
UK Stageurs
UK Gents
UK Ladies
So those 3 items will have to be in USERS column that matches the group
so above should be Column E

So
Joe Does does not have all 3
Karen Dean , has UK Ladies & UK Gents - BUT NOT UK Stageurs
George Gold - only has UK Gents

On Joe Doe - Why is N on ROW 6 - not sure which row you want to use to flag NO

in the Missing groups - that would potentially be huge

I'm really not sure how to help here - Sorry