Create List based on Row, Column and "X" Value in table

[YoungConner]

Hello, I have spreadsheet where I want to create a list based on the X values in the table. I already have a macro that will create the list but I also need to macro to include the row header.

Here is the spreadsheet table

This is the list I want to create

This is the VBA I currently have:
Sub Test1()
Dim rng As Range
Dim r As Integer
Dim c As Integer
Dim lastRow As Integer
Dim lastRow2 As Integer
Set rng = Range("B2:F8")
For Each dng In rng
lastRow = Range("H" & Rows.Count).End(xlUp).Row
lastRow2 = Range("I" & Rows.Count).End(xlUp).Row
If dng.Value = 1 Then
r = dng.Row
c = dng.Column
Range("H" & lastRow + 1).Value = Cells(r, 1).Value
Range("I" & lastRow2 + 1).Value = Cells(1, c).Value
End If
Next dng
End Sub

 

[alansidman]

Can be accomplished using Power Query to Unpivot your data.