Multiple Rows with inconsistent structure into One Row without use of macros

Can someone help me to write a formula that can produce the attached?

The Input Sheet provides an example of the initial data set.
The Output Sheet provides the desired result data set.

Clearly, this can be accomplished with a macro, but I am trying to see if it can be accomplished without writing a macro.

Any help would be appreciated greatly.

Brian