As far as a formula, there might be a way... but, it would be way complex. VBA would be a viable solution.
Or, if you're interested, I can offer you a few steps to get the data in the way you mention. Just know, it would be a multiple step process. Let me know if you'd like the steps or if you'd rather hold out and see if someone posts some VBA for you.