Excel, conditional function to calculate next value within a list based on boolean

Question

Sorry if the title isn't clear enough. I didn't find a nice way to phrase what I'm trying to achieve here. I'm an excel newbie so don't know where to start with the logic for what I'm looking for.

I have a spreadsheet I want to use to schedule cleaning in a student corridor. Thing is that rooms are not always occupied, so it needs to be able to react whether the next room is occupied or not and if not, select next one on the list.

In this image we can see how week 22 was cleaned by 1902 and 1903, next week turn should be next two rooms in [1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911] as long as they are booked (value 1 in the columns of the right). 1904 and 1905 are booked (green) so next week will be their turn.

If we continue with the same logic next turn will be 1906&1907 and next week (weeknumber 25) should be 1908&1909, problem is that room 1908 is empty right now so it shouldn't be counted as an eligible room and instead choose 1909&1910 as next cleaning rooms.

How could I achieve this logic with an excel spreadsheet?

Here is a link for a copy of my spreadsheet so you can see what I'm trying to achieve and play with original data if needed.

Use this formula to find the next available room: =INDEX($G$1:Q$1,MATCH(TRUE,INDEX(G2:Q2=1,0),)) — Mahmood Shehab, Jun 15 '21 at 21:52
Hi! I tried that and in only returns single next room and always the same one, 1901. Are you sure is okay? — eljiwo, Jun 16 '21 at 08:51
What version of Excel are you using? Specifically, does your version support Dynamic Arrays? — chris neilsen, Jun 20 '21 at 22:14

score 0 · Answer 1 · answered Jun 20 '21 at 13:00

Approach

Identify last room cleaned
Create a list of the occupied rooms that would come next
Identify the next two occupied rooms to be cleaned
Handle situation if less than two rooms are booked

Step 1: Identify last room cleaned

I created a new tab called "Setup" in which the user can select the last room that has been cleaned, from a drop down list. The cell $C$3 is accessible via range name setup_lastcleaned_room.

Cell $F$2 on the main tab refers to this =setup_lastcleaned_room.

Step 02: Create a list of the occupied rooms that would come next

Column U (yellow area) refers to the last room cleaned, which is determined in column F one row above. We look into this formula in step 04.

Column V (orange area) determines the position of the "last room cleaned" within the list of rooms =MATCH(U3,list_rooms,0) with list_rooms being the range name for "=Schedule!$I$1:$S$1" (the tab's name is "Schedule").

Columns W to AG (light green area) utilize the OFFSET-Formula to list the rooms that would have to be cleaned next, if they were occupied.

=IF($V3+W$1<=11,IF(OFFSET($H$1,ROW()-1,$V3+W$1)=1,OFFSET($H$1,0,$V3+W$1),""),IF(OFFSET($H$1,ROW()-1,$V3+W$1-11)=1,OFFSET($H$1,0,$V3+W$1-11),"")).

Looks complicated? Let us break it into pieces:

=IF(last_room_on_list_not_reached, handle_end_of_list, handle_beginning_of_list).

last_room_on_list_not_reached = $V3+W$1<=11: We check if the "last room cleaned" plus the "x-th following room" would exceed our list of rooms.
handle_end_of_list = IF(OFFSET($H$1,ROW()-1,$V3+W$1)=1,OFFSET($H$1,0,$V3+W$1),"") respectively: IF(is_room_booked,show_room_name,"")
is_room_booked = OFFSET($H$1,ROW()-1,$V3+W$1)=1: We move the anchor cell ($H$1) by the current number of rows minus one (the row number of the anchor) and by a number of columns determined by "position of last room cleaned" + "x-th following room". Next, we check if this room is booked (cell content = 1).
show_room_name = OFFSET($H$1,0,$V3+W$1): This time we move the anchor cell by the number of columns only (see above) to get the room name (which is in the same row as our anchor).
handle_beginning_of_list: similiar to "handle_end_of_list", but this time the columns are determined slightly different.

Column AH (dark green area) concatenates all room names together to achieve a string of the next following room names: =W3&X3&Y3&Z3&AA3&AB3&AC3&AD3&AE3&AF3&AG3&"NONENONE". We also add two placeholders ("NONE") to the string to get results even if less than two rooms are booked. Please note This solution takes into account that all room names (and placeholders) comprise of a fixed number of characters (four). If this would not be the case, we would have to include a delimiter and the following formulas would be slightly more complex.

Step 3: Identify the next two occupied rooms to be cleaned.

Having all four-character room names in the desired order, it is easy to determine the next two rooms that need to be cleaned.

Columns E (light blue area) and F (dark blue area) retrieve the first resp. the second room that should be cleaned this week: =MID($AH3,1,4) resp. =MID($AH3,5,4).

Column D (purple area) concatenates both rooms with a dash to recreate the questioner's sample: =E3&"-"&F3.

Step 4: Handle situation if less than two rooms are booked

Each week, we determine the last room that has been cleaned. But what if none or only one was booked? We could not determine a valid room and hence, would not know with which room to continue. Thus we check for the second room that has been cleaned one row above and if it is "none", we look for the first one. If that is also "none", we take the room that was defined in setup_lastcleaned_room: =IF(F2="NONE",IF(E2="NONE",setup_lastcleaned_room,VALUE(E2)),VALUE(F2)). Notes: We use "VALUE" as the room names are numbers but were turned to strings when they were concatenated.

The result file is available at: https://tmpfiles.org/dl/57572/stackoverflow_pax19floorcleaningschedule.xlsx (for 60 mins) — Qualia Communications, Jun 20 '21 at 13:29

sbgib · Accepted Answer · 2021-06-20T18:19:17.833

Short answer

I'm fairly sure it's this:

=IF(NOT(ISNA(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1)), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1) & "-" & IF(NOT(ISNA(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1)))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-1,COLUMNS($G$1:$Q$1))+1-2)),0))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0)) & "-" & IF(NOT(ISNA(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1)))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1))+1)):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1))),0)+MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1))), INDEX($G$1:$Q$1,1,MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(1,INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1))):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1) + MOD(MATCH(INT(RIGHT(D5, LEN(D5)-FIND("-", D5))),$G$1:$Q$1,0),COLUMNS($G$1:$Q$1))+1-2)),0),COLUMNS($G$1:$Q$1))+1-2)),0))))

Paste this in below a row which already has a turn in it, e.g. D6 (the turn for week 27). This formula uses the previous turn and the range of room names ($G$1:$Q$1) to determine what the next turn should be.

Long answer

Wait, what is this monstrosity!? It's the most daunting formula I've ever seen too!

Well, there's a lot of repetition in there so that it works independently. It's also doing a lot of heavy lifting so that it's e.g. flexible to how many rooms there might be, what the present row is and to any rooms being booked. There could be even more logic to handle edge cases such as, what if all rooms are booked? (currently it returns #N/A), but this solves the fundamental problem.

Breaking it down into steps:

Find which room was cleaned last

Using cell D6 as a starting point, the last room to be cleaned is 1910. So, we want to find this value in cell D5:

=INT(RIGHT(D5, LEN(D5)-FIND("-", D5)))

This simply takes the text from D5 and extracts the last number.

Locate that within the range

The range $G$1:$Q$1 is the list of cells on the first row that specify the room numbers ($ means that this is fixed, so if you drag this formula down, for example, the range will still refer to G1:Q1 and not anything below it).

The 1st column of this range (G) is the 7th column in the spreadsheet. This (7) can be found with the following formula:

=COLUMN($G$1:$Q$1)

The number of columns in the range (11) is:

=COLUMNS($G$1:$Q$1)

To find the last cleaned room in the range, we use MATCH with Match_type = 0:

MATCH finds the first value that is exactly equal to lookup_value.

=MATCH( [Last room number] ,$G$1:$Q$1,0)

=MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0)

This returns the relative column number, from 1 to 11, of the the matching cell (1910 is on the 10th column).

Identify target range

We now want to start looking at the next column after that. We could simply add 1, which works fine unless the last room to be cleaned was 1911. In that case, we can't just add 1, because there's no 12th room. There, we instead want to go back round to the 1st room. To do this, we first use MOD and divide by the number of columns:

=MOD( [Relative column number] ,$G$1:$Q$1,0), [Column count] ) +1

=MOD( MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) +1

The number returned by this is the column to start with. So, if the last room was 1910, then we get 11 as a result. The column to end with is the last one, 11 (the column count).

However, if no unbooked rooms are found in this range, then we have to go around and check again from the first to the one before the start. This means we always need to do two checks to maintain the sequence of rooms.

In our example:

Check	Start	End	Range
1	11	11	Q6:Q6
2	1	10	G6:P6

As pseudo-formulae:

Check	Start	End	Range
1	MOD( [Relative column number] ,$G$1:$Q$1,0), [Column count] ) +1	[Column count]	=INDIRECT(ADDRESS(ROW(), [Column count before first] + [Start] )):INDIRECT(ADDRESS(ROW(), [Column count before first] + [End] ))
2	1	[Last room column]	=INDIRECT(ADDRESS(ROW(), [Column count before first] + [Start] )):INDIRECT(ADDRESS(ROW(), [Column count before first] + [End] ))

As formulae:

Check	Start	End	Range
1	`=MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) +1`	`=COLUMNS($G$1:$Q$1)`	`=INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1)-1 + MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) +1 )):INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1) ))`
2	`1`	`=MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) )`	`=INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1) )):INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1)-1 + MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) ))`

Some formula acrobatics needs to be done to describe this as a range to Excel. These ranges are specifying the present row, and giving the absolute column reference (remember, the 1st column of the range is the 7th column of the spreadsheet, so we need to add 6, or =COLUMN($G$1:$Q$1) -1 to these)

Find next unbooked room

Now we can use that with MATCH to find the next unbooked room in each case:

=MATCH(1, [Range] ,0) + [Start] -1

=MATCH(1, INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) +1 )):INDIRECT(ADDRESS(ROW(),COLUMN($G$1:$Q$1)-1 + COLUMNS($G$1:$Q$1) )) ,0)+ MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) )

(Returns 11)

=MATCH(1, [Range] ,0)

=MATCH(1, INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1) )):INDIRECT(ADDRESS(ROW(), COLUMN($G$1:$Q$1)-1 + MOD(MATCH( INT(RIGHT(D5, LEN(D5)-FIND("-", D5))) ,$G$1:$Q$1,0), COLUMNS($G$1:$Q$1) ) )) ,0)

(This is slightly simpler as start is always 1)

(Returns 1)

This means that a value of 1 has been found on the 11th and 1st columns.

Finding room numbers

To find the actual room numbers from these column numbers, we use INDEX:

=INDEX($G$1:$Q$1,1, [Matching column number]) Returns 1901 and 1911 for the above.

Conditional logic

If the first check found a number (returning #N/A otherwise), we don't need the second. In other words, if the next unbooked room was found after the last one, then we don't need to go round to room 1901 to continue checking. In Excel, this is an IF function:

=IF(NOT(ISNA( [Check 1] )), INDEX($G$1:$Q$1,1, [Check 1]), INDEX($G$1:$Q$1,1, [Check 2]))

However, we are searching for two rooms, which may be in the first and second check, or both be within the first check, or both within the second, or otherwise not found. As MATCH only finds the first match in the range, we need to do this once, adjust the range based on the result, then do this all again. The overall formula becomes a combination of IF functions, like this:

=IF(NOT(ISNA( [Check 1] )),

INDEX($G$1:$Q$1,1, [Check 1]) & "-" & IF(NOT(ISNA( [Check 3] )), INDEX($G$1:$Q$1,1, [Check 3]), INDEX($G$1:$Q$1,1, [Check 4])),

INDEX($G$1:$Q$1,1, [Check 2]) & "-" & IF(NOT(ISNA( [Check 3] )), INDEX($G$1:$Q$1,1, [Check 3]), INDEX($G$1:$Q$1,1, [Check 4]))

)

When you combine all of that, the result is the big formula from the beginning.

Alternative answer

This actually looks much simpler in VBA code. Here is a guide on how to set this up in Excel. Use the function below:

Public Function choose_turns(last As Range, rooms As Range) As String
    Dim row As Integer
    Dim rooms_first_column As Integer
    Dim rooms_count As Integer
    Dim last_room_number As Integer
    Dim last_room_column As Integer
    Dim count_rooms_found As Integer
    Dim start_column As Integer
    Dim end_column As Integer
    Dim found_column As Integer
    Dim target As Range
    Dim result As String
    
    'Find which room was cleaned last
    last_room_number = Int(Mid(last.Value, InStr(last.Value, "-") + 1))
    
    'Locate that within the range
    rooms_first_column = rooms.Column
    rooms_count = rooms.Columns.Count
    last_room_column = Application.Match(last_room_number, rooms, 0)
        
    'Conditional logic
    row = last.row + 1
    count_rooms_found = 0
    start_column = last_room_column
    
    Do While count_rooms_found < 2
        found_column = 0

        'Check 1

        'Identify target range
        start_column = (start_column Mod rooms_count) + 1
        end_column = rooms_count
        Set target = Range(Cells(row, rooms_first_column - 1 + start_column), Cells(row, rooms_first_column - 1 + end_column))
        
        'Find next unbooked room
        If Not IsError(Application.Match(1, target, 0)) Then
            found_column = Application.Match(1, target, 0) + start_column - 1
        Else
            'Check 2

            'Identify target range
            start_column = 1
            end_column = last_room_column
            Set target = Range(Cells(row, rooms_first_column - 1 + start_column), Cells(row, rooms_first_column - 1 + end_column))
            
            'Find next unbooked room
            If Not IsError(Application.Match(1, target, 0)) Then
                found_column = Application.Match(1, target, 0) + start_column - 1
            End If
        End If
        
        If found_column = 0 Then
            Exit Do
        End If
        
        If 0 < Len(result) Then
            result = result + "-"
        End If
        
        'Finding room numbers
        result = result + CStr(Application.WorksheetFunction.Index(rooms, 1, found_column))
        
        count_rooms_found = count_rooms_found + 1
    Loop
    
    choose_turns = result
End Function

Then, in your worksheet, you can use it as a formula like this (e.g. in D6):

=choose_turns(D5, $G$1:$Q$1)

score 0 · Answer 3 · answered Jun 20 '21 at 23:04

If you have a version of Excel that support Dynamic Arrays, consider (in D2 and copy down)

=LET(Rooms,$G$1:$Q$1,
Use,$G2:$Q2,
Used,FILTER(Rooms,Use=1),
Avail,COUNT(Used),
Cols,SEQUENCE(1,Avail*2, 1, 1),
Roster,INDEX(Used, 1, IF(Cols<=Avail, Cols, Cols-Avail)),
Prev,IF(D1="Turn", INDEX(Used, 1, Avail), --MID(D1,6,4)),
First,INDEX(Roster, 1, XMATCH(Prev, Roster, -1)+1),
Second,INDEX(Roster, 1, XMATCH(First, Roster, 0)+1),
First&"-"&Second)

Explanation

Rooms - Range refering to Names of the rooms
Occupancy - Range of room occupancy for the week in question
Used - Filtered list of occupied rooms for the week
Avail - count of occupied rooms
Cols - sequence 1 to Avail * 2. Used to index the Used` list
Roster - An array of the Used rooms, repeated (so we don't have to worry about wrapping)
Prev - the second room in the previous row result
First - the first result. Index into the Roster from the Prev room
Second - the second result. Index into the Roster from the First room

_{Note: this will return errors if only 0 or 1 rooms are occupied in any week. Is this a possibility, and if so, what is your expected result?}

Demo