I have 3 columns of data, "room name" (F), "event date" (G) and "event start time/type" (H)
I am rearranging the data (sort of like a pivot table) in a different sheet that will list unique Room Names in column A with header in cell A6 and Event Dates across row 6. I need a UDF that, given the Room Name and Event Date, will return all matching values for Event Start Time/Type to fill in a grid, with chr(10) separating the values. A pivot table, at best, will only count the number of events that are taking place in a specific room on a specific date instead of showing what they actually are. The point of this project is to convert an automated report into a format requested by our clients, so changing the layout really isn't an option.
So far, I've been able to find a UDF and corresponding formula that will return a value based on multiple criteria, and I've been able to find a UDF and formula that will concatenate all results from a lookup with a single criteria, but I cannot find anything that will do both. Every time this table is created, there will be a different number of date ranges being queried, and a different number of room names listed. I'd rather avoid hidden columns, and I'll probably add a macro that will paste the formula into cell B7, and copy it over to the last date and down to the last row room names
This is the Macro that I found for concatenating results http://www.get-digital-help.com/2010...into-one-cell/
and this is the code with the multiple lookups (http://www.excelguide.eu/index.php
I have no idea how to adapt them to work together.
I've attached a sample to help better visualize how it's supposed to look.
Any help will be so greatly appreciated!
Thanks so much!