Check for values in a table and if found add value found in column to left to list

Hi

I have a table of validated list drop downs, there are four for people to choose from and all are days of the week so they choose their options.

So for example I would like to be able to list all the people who chose Monday as an option. The table is C8:G25 and C is the column with the persons names and D, E, F and G are columns with the days of the week option drop downs. Im also interested to know if possible to list all the people who have not picked a day.

Ive attached a mock up spreadsheet with the output im looking for hard text in

I really appreciate your help with this and hope its possible.

Thanks Rob

Hello Rob
Does the table in the attached reply do as you require? The formulas could be reversed for people Not choosing those days.

Hope this helps.
DBY

Thanks this is great,

DBS can you tell me which bit of the formula would I need to change to get a list of the people who haven't chosen.

Fotis the array looks like a useful formula - could you help break down the array formula into simple terms based on my question so I can understand how to use and adapt this too

Cheers

Hello Rob
I apologise, it's not just a case of reversing the formulas as I thought, didn't think it through. The only way I can think of at the moment to bring back the negatives for each day is to use a separate count table. In the attached reply I've done that and created a few Named ranges for ease of reference. You could use Fotis's Array formulas with this table if you wish, it's a matter of preference if you're happy to use them.

Perahps Fotis or another forum member might have a solution for returning the negative days without a helper table.

Regards
DBY

Thanks guys this is really really useful, the array formulas are very powerful. DBY thanks for your solution, although ive learnt something especially new from Fotis I will use your suggestion on with the lookup to get the neg days.
Best regards,,,
Rob