Find a value within multiple variables?

[RNIB]

We use a rather complex spreadsheet to work out our staff rota and I'm trying to write a macro that will compose an e-mail to tell each member of staff where they need to be in plain English.

I have an Excel spreadsheet which has several columns indicating different times of day for each day of the week.

For each column there are several rows indicating a room

So for example:
B23 is Monday Morning in Studio 1
B24 is Monday Morning in Studio 2
B25 is Monday Morning in Studio 3
B26 is Monday Morning in Studio 4
B27 is Monday Morning in Studio 5
etc down to
B48 is Monday Morning in PP1

In the cells B23:B48 a persons name will appear but that name can only appear once within that range (the spreadsheet won't let you double book a person).

I'm using DDERequest to extract all the contents of these cells and store each one as a variable.

What I'm struggling with is how to now get this information into an e-mail. Ideally I want to compose the e-mail in the following way:

Bob:
Monday Morning you are in Studio 1
Monday Afternoon you are in PP1
Tuesday Morning you are Studio 3
Tuesday Afternoon you are in Studio 2
Wednesday Morning you are in PP2
Wednesday Afternoon you are in PP2
Thursday Morning you are out of office
Thursday Afternoon you are in Studio 6

etc and repeat this for all members of staff

I'm thinking that I need to search all the variable that have been created for all the cells within a given column to see if any of them equal a specific name. If it does it needs to identify which variable it is that contains that name and know that if it does that that variable refers to a specific room, day and time. Essentially a bunch of nested IF's and Let commands but I can't think how to write it.

[adroege]

So for example:
B23 is Monday Morning in Studio 1
B24 is Monday Morning in Studio 2
B25 is Monday Morning in Studio 3
B26 is Monday Morning in Studio 4
B27 is Monday Morning in Studio 5
etc down to
B48 is Monday Morning in PP1

In the cells B23:B48 a persons name will appear but that name can only appear once within that range (the spreadsheet won't let you double book a person).

Seems like the ranges are not listed correctly as B23 can't have "Monday Morning in Studio 1" and "Bob" at the same time?

Can you provide a link where we can download a sample spreadsheet?
I tried to recreate your data from your description but could not.

Thanks.