Error Checking Formula

[Danger_SF]

On the attached workbook, I have 4 rows. These 4 rows detail the state of 2 offices, with 2 desks each.

Office 300 has two desks. One is Occupied and one is Vacant.

Office 301 has two desks. Both are Occupied.

In all offices, I want to indicate a Seat as “Alone” if the second desk in the room is Vacant.

In all offices, I want to indicate a Seat as “Shared” if the second desk in the room is also Occupied.

I’m currently double checking my work manually, because I can’t find a way to create an Error Checking Formula (ECF) to do this for me. If I had an ECF that checked this for me, I would check the ECF before I saved the workbook, go back to see what I did wrong, and correct it.

Can someone help me create an ECF like the example shown, to accomplish this task?

Alternatively, if there’s a way for the “Seat” cell to automatically populate when criteria is met, that would be even cleaner.

[#2]

@Danger_SF 

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.

[#3]

@Hans Vogelaar 

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.

[#4]

@Danger_SF 

I don't understand the distinction between "No" and "Alone".

[#5]

@Hans Vogelaar 

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.

[#6]

@Danger_SF 

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.

[#7]

@Hans Vogelaar 

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?

[#8 · Accepted solution]

@Danger_SF 

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")

[#9]

I'm impressed. Thanks Hans. I appreciate you.

[#10]

@Hans Vogelaar 

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?

[#11]

@Danger_SF 

=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.

[#12]

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.