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")
In G2:
=IF(A2="*None","V",IF(COUNTIFS($B$2:$B$100,$B2,$A$2:$A$100,"*None")=0,"Shared","Alone"))
Adjust the ranges if the data extend below row 100.
Fill down.
Hans, I really appreciate your response. Thank you!
Here's where you'll begin to dislike me....
What if I wanted the text in cell G2 to be:
"V" if the text in cell A2 is *None
"Comm", if the text in cell A2 is Common
"Hotel", if the text in cell A2 is Hotel
"No", if the text in cell A2 is anything other than Common, Hotel, or *None
and
"Shared" if the value in B2 has any duplicates anywhere else in column B
or
"Alone" if the value in B2 does not have any duplicates anywhere else in column B
I apologize for not asking this originally. I'm assuming a different formula is needed, based on the concept of a logical test being either one thing or another; Not one of 4 things or another.
I'd appreciate any help you can provide.
I don't understand the distinction between "No" and "Alone".
Hi Hans!
This spreadsheet tracks all types of space within an office complex. Seats for employees, as well as other spaces.
"Alone" represents a space occupied by an employee where he resides in the space by himself.
"No" Is an arbitrary term indicating that the subject Excel row represents a space other than a seat for an employee. Examples would be a janitor closet, hallway, lobby, etc.
In G2:
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",COUNTIFS($B:$B,$B2,$A:$A,"*None")=0,"Shared",TRUE,"Alone")
Please check carefully.