If you are willing/allowed to use VBA, check out this approach.
Click button on sheet "Results"
Sub Listing()
Dim dict As Object, cl As Range, myRow As Long, myColumn As Long
myRow = Worksheets("Example").Cells(Rows.Count, 1).End(xlUp).Row
myColumn = Worksheets("Example").Cells(1, Columns.Count).End(xlToLeft).Column
Set dict = CreateObject("Scripting.Dictionary")
For Each cl In Worksheets("Example").Range("B2:" & Cells(myRow, myColumn).Address) 'modify worksheet names as necessary
If cl.Value <> "" Then
dict.Item(dict.Count) = Array(cl.Value, Worksheets("Example").Cells(cl.Row, 1).Value, Worksheets("Example").Cells(1, cl.Column).Value)
End If
Next cl
Worksheets("Results").Range("A1").CurrentRegion.ClearContents
Worksheets("Results").Range("A1").Resize(dict.Count, 3) = Application.Index(dict.Items, 0, 0)
Set dict = Nothing
End Sub
Bookmarks