Ok, without some overly painful VBA programming I do not think this is possible in Excel. That said, I took your data, changed the entry format to Company, State, City, Date, Times and moved it into an Access database. It has a simple tool that generates cross tab queries like a pivot table with some differences. The resulting query can than be exported or copied into Excel if you need to share it. Attached is the sample database.

Monkey(TestDB).zip

Best of luck.