How can I create a Smartsheet formula that automatically flags the empty bed in a shared room?

surveysHMG

"I have a Smartsheet with a 'Room-Bed' field where rooms are shared, such as '102 - D' and '102 - W.' Another field, 'Gender,' identifies each occupant as either 'Male' or 'Female.' When only one bed in a room is occupied, I need to automatically flag the empty bed with the same gender as the occupied one. The 'Empty Rm Gender' should deplay a "M" for Male and "F" for Female. But i am defanetly happy with it displaying "Male" and "Female". My current formula does just that. The problem is that it function correctly if the first instance of the room is "Occupied" and the second instance is "Empty", but it breaks down when the the first instance of the room is "Empty" and the second instance is "Occupied". Can someone please help me?
Room-Bed = "102 - D" or "102 - W" on up to "450" - D and "450 - W"
Bed Status - "Occupied" or "Empty"
Gender = "Male" or "Female"
Rm_Gender_Helper = =LEFT([Room-Bed]@row, FIND("-", [Room-Bed]@row) - 2) which returns just the room number so identifies the room and disregards the bed
Empty Rm Gender = this is the field that the formla is in.

=INDEX(Gender:Gender, MATCH([Rm_Gender_Helper]@row, [Rm_Gender_Helper]:[Rm_Gender_Helper], 0))

I've even tried the below formula as well but it produces the same result
=IF([Rm_Gender_Helper]@row = "",
INDEX(Gender:Gender, MATCH([Rm_Gender_Helper]@row, [Rm_Gender_Helper]:[Rm_Gender_Helper], 0)),
INDEX(Gender:Gender, MATCH([Rm_Gender_Helper]@row, [Rm_Gender_Helper]:[Rm_Gender_Helper], 1))
)

=IF(
COUNTIFS([Room Number]:[Room Number], [Room Number]@row, [Gender]:[Gender], @cell) = 1,
INDEX(Gender:Gender, MATCH([Room Number]@row, [Room Number]:[Room Number], 0)),
INDEX(Gender:Gender, MATCH([Room Number]@row, [Room Number]:[Room Number], 1))
)

=IF([Bed Status]@row = "Empty",
IFERROR(
INDEX(Gender:Gender, MATCH([Rm_Gender_Helper]@row, [Rm_Gender_Helper]:[Rm_Gender_Helper], 0)),
INDEX(Gender:Gender, MATCH([Rm_Gender_Helper]@row, [Rm_Gender_Helper]:[Rm_Gender_Helper], 1))
),
""
)

Paul Newcome

Does this one work for you?

=IF([Bed Status]@row = "", INDEX(COLLECT(Gender:Gender, Gender:Gender, @cell <> "", [Rm_Gender_Helper]:[Rm_Gender_Helper], @cell = [RM_Gender_Helper]@row), 1))

Paul Newcome

Does this one work for you?

=IF([Bed Status]@row = "", INDEX(COLLECT(Gender:Gender, Gender:Gender, @cell <> "", [Rm_Gender_Helper]:[Rm_Gender_Helper], @cell = [RM_Gender_Helper]@row), 1))

Corey W.

How about this in your room gender field:

=JOIN(COLLECT(Gender:Gender, [Room-Bed]:[Room-Bed], LEFT(@cell, 3 ) = LEFT([Room-Bed]@row, 3), [Bed Status]:[Bed Status], "Occupied"))

Corey W.

Or this if the double genders in fully occupied rooms are a problem:

=IFERROR(JOIN(INDEX(COLLECT(Gender:Gender, [Room-Bed]:[Room-Bed], LEFT(@cell, 3 ) = LEFT([Room-Bed]@row, 3), [Bed Status]:[Bed Status], "Occupied"), 1)), "")

surveysHMG

Hi Corey and Paul,

Thank you both for your prompt suggestions! Corey, unfortunately, both of your formulas returned blanks in the empty rooms. 😔

Paul, I think I can get yours to work with a small tweak. I replaced the "" with "Empty," and the formula mostly worked, but it errors on rooms marked as "Private" in the "Room Type" field, which I didn’t mention earlier. I believe I can easily account for that.

Thanks again for your help! 😊

surveysHMG

Hi Corey and Paul,

Thank you both for your prompt suggestions! Corey, unfortunately, both of your formulas returned blanks in the empty rooms. 😔

Paul, I think I can get yours to work with a small tweak. I replaced the "" with "Empty," and the formula mostly worked, but it errors on rooms marked as "Private" in the "Room Type" field, which I didn’t mention earlier. I believe I can easily account for that.

Thanks again for your help! 😊