Bundle values

I have a simple schedule for reservation of hotel rooms. I mark for every day (with a cross) wich rooms to book. On the the right side I want to represent
each room after each other like in my example. How do I accomplish that? I do not want to use matris formula. It can be from one single room up to
maximum 6.

Something like this?

PS. Welcome to the forum, but please try to attach workbooks rather than pictures - it saves people having to re-type your data.

Yes exactly, now I have to understand how you solved It. Thank you very much.

@ Tobbe

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Well, if it helps, looking at the formula in cell J6 of my file:

=IF(COLUMN(A:A)>COUNTA($A6:$F6),"",INDEX($A$4:$F$4,1,LARGE(INDEX(COLUMN($A6:$F6)*--($A6:$F6<>""),0),COUNTA($A6:$F6)-(COLUMN(A:A)-1))))

The COLUMN(A:A) (which appears twice) is just a counter - COLUMN(A:A) returns 1, when we drag this formula cell to the right Excel will updated it to COLUMN(B:B) which will return 2 - so it's just there to keep track of which column we're in.

COUNTA($A6:$F6) (which also appears twice) counts how many cells in that range aren't blank, because that's also the number of columns we'll have to populate with an answer.

So, the first part of the formula:

=IF(COLUMN(A:A)>COUNTA($A6:$F6),""

Just says that if the column we're up to is more than the number of populated columns then return a blank.

If the counter isn't above the number of populated columns then we need to return a value:

INDEX($A$4:$F$4,1,

Says that we're going to return a value from row 1 of the range A4:F4 (the room numbers), so the only tricky bit is working out which column we have to return.

LARGE(INDEX(COLUMN($A6:$F6)*--($A6:$F6<>""),0),COUNTA($A6:$F6)-(COLUMN(A:A)-1))

The last bit of that portion of the formula, COUNTA($A6:$F6)-(COLUMN(A:A)-1), is subtracting the column we're up to, minus 1, from the number of filled columns. So if 5 rooms are booked then, as we drag this formula across, this portion of the formula will count down 5, 4, 3, 2, 1. You'll see why we need to do this in a second.

The middle bit, INDEX(COLUMN($A6:$F6)*--($A6:$F6<>""),0), is creating an array of values. For each room column it's multiplying the column number by either 1 if that cell isn't empty, or by zero if it is empty. So if columns A, C and E each have a cross in we end up with an array that looks like {1,0,3,0,5,0}, and if only columns A and B had an x in then the array would look like {1,2,0,0,0,0}.

LARGE is taking that array an returning the n-th largest number from it. So, for example, LARGE({1,2,0,0,0,0},2) would return the 2nd largest number from the array {1,2,0,0,0,0}, which would be 1.

We're feeding our countdown into the LARGE, so in the first column of this formula we'll return the title from the lowest populated column, and Bob's your auntie's good friend.

If all of this seems a massive fuss to do something you'd expect to be straightforward then welcome to the wonderful world of Excel formula