Forum Discussion
Error Checking Formula
Up to now, your examples didn't have "One Desk Setup", so the formula didn't take that into account.
New one:
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",OR(C2="One Desk Setup",COUNTIFS($B:$B,$B2,$A:$A,"*None")>0),"Alone",TRUE,"Shared")
Interesting. I see what you did with the formula; making it search for the word, "Desk".
I added two rooms with a sole occupant.
Why doesn't it see rooms 200 and 201 as unique, when they also have the word "Desk" in column C?
Up to now, your examples didn't have "One Desk Setup", so the formula didn't take that into account.
New one:
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",OR(C2="One Desk Setup",COUNTIFS($B:$B,$B2,$A:$A,"*None")>0),"Alone",TRUE,"Shared")
- I appreciate your patience with me and I apologize for making extra work for you. I am sincerely grateful for your knowledge and for your time.
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",C2="One Desk Setup","Alone",COUNTIFS($B:$B,$B2,$A:$A,"<>*None",$A:$A,"<>*Common",$A:$A,"<>*Hotel")>1,"Shared",TRUE,"Alone")
PS, a request: if you have more variants in the future: please provide a sample workbook with all possible combinations instead of just the "new" one.
So, there will be instances where there are 6 seats in an office.
2 Vacant
4 Occupied
The 4 occupied seats should show as "Shared".
This formula marks the 4 occupied seats in this same office as "Alone".
Can you help?- I'm impressed. Thanks Hans. I appreciate you.