Using excel to return a list of values based on multiple inputs

[annisd]

Hi All,

I've got what I feel is a somewhat complex array formula that I'd love to get some help with.  Essentially, I'm trying to create a visual way of representing scheduling data for an academic unit of a large college. I've got one tab which has raw data including 2 values that I want to have returned in a visual format. I've attached the spreadsheet I've started with if its helpful. This is incredibly rudimentary because the spreadsheet I want to build can't be done without working out this formula first, but I'm hoping it will help you to understand what I'd like to do. 

Tab 1 is a stab at how I'd like it laid out including what I'd like to have the formula do automatically highlighted in yellow. Tab 2 is how the raw data might look.

In Image 1, I'd like excel to look at A1 and A2 to return all instructors and courses with courses for that day on Image 2. Each timeslot on each day is limited to a maximum of 23 courses, so the formula really has to be able to return any entries that matched the criteria. The complete spreadsheet would enable us to take raw scheduling data provided by faculty and immediately have an idea of how many time slots were over scheduled. 

I'm a somewhat intermediate excel user so it's entirely possible that what I'm describing is possible in other ways that elude me. I would be so grateful for any help you can provide!

[#2]

Is the raw data always in this particular layout (three columns with weekdays, 1 with time, one with name)? If not, what variations can we expect?

[#3]

@annisd 

Let me add to Jan's questions. Are the class periods standardized, such that you could refer to them as Period 1, Period 2, etc., along with days of the week? Or do the times vary for some?

[The reason for asking; it would be easy to have a separate table that provides the detail

• Per 1 = 8:00 -- 9:20
• Per 2 = whatever
• etc

Doing something like this would streamline/simplify the input process. And provide flexibility for future changes to the configuration of the day. Period 1 would always be Period 1, but its hours could change.)

Would it not make sense to add a column for Class Size (or are all classrooms the same size)? And aren't some classes such that they require a specific space (chemistry, perhaps) ...or is that just not part of your "business need"?

Or are all those part of the greater complexity you allude to? I appreciate that you describe yourself as "a somewhat intermediate excel user." I used to think of myself (before I retired in 2002) as an advanced user, but since discovering this site a month or so ago, I've discovered that Excel has added features many features in the decade plus since I retired--including the kinds of things you will no doubt be hearing about from some of the true experts here--so I"m looking forward too to, e.g., how Power Query might serve to extract the report you need.

But you'd probably get more genuinely helpful advice from the advanced users here if you added descriptions of the kind of complexity that you already know about...describe (or give a sample of) the various input details as well as the full output, the user questions that need to be answered.