You have a pivot table layout that you want to "un-pivot". This article by Jon Walkenbach shows what you can do with just a few keystrokes and no VBA code is needed.
For your specific situation here's what I would do.
1.Open up the "old" pivot table menu using Alt + D, P. You'll get a dialog box like that shown below. Choose the "Multiple consolidation ranges" option and click "Next".
2.On the next step choose "I will create the page fields"; click "Next".
3.Highlight your range of cells so it is defined in the upper part of the dialog box, then click the "Add" button to copy that range to the "All ranges" list. Click "Next".
4.Choose a cell away from your data. Here you see I've chosen F3 on the same sheet. (YOu can choose a different sheet if you like.) Click "Finish".
5.You'll get a pivot table like this. In the dialog box, un-check the "Row" and "Column" checkboxes.
6.That gives you a one-cell pivot table.
7.You will then double-click the one cell, and on a new sheet, you will get your "un-pivoted" data as a new table:
With this table data, you can now filter column C to show the non-blanks, and you can delete column B, and you'll have your desired layout.