Populating List from Table

[Tommyslats]

Hi Everyone,

I want to be able for Excel to populate into a list format from a table where 'X' is marking the item. I've attached a test so you can see. So you can see in cell D2 there is 'X' marked.

Down below you can see the result I am after. It is a concatenation of the X & Y axis along with the column B.

Any ideas?

[MarvinP]

Hi Tommy,

This was about a 5 click problem if you have Power Query. Do you have a version of Excel that has Power Query?
Steps:
1. Click in Table
2. Click on Data Tab and then on "From Table or Range"
3. Select first 3 columns and then Right click and click on Unpivot Others
4. Click on Close and load to
5. Click OK ... Done

No formulas needed no VBA
Unpivot Others Keeps X.xlsx

[Tommyslats]

Hi Tommy,

This was about a 5 click problem if you have Power Query. Do you have a version of Excel that has Power Query?
Steps:
1. Click in Table
2. Click on Data Tab and then on "From Table or Range"
3. Select first 3 columns and then Right click and click on Unpivot Others
4. Click on Close and load to
5. Click OK ... Done

No formulas needed no VBA
Attachment 667813

I'm using Excel from OFFICE 365 on a Mac.
But I cannot seem to find any reference to Power Query or Get & Transform?
There is no option for 'From Table or Range'

[JeteMc]

As you don't have Power Query then perhaps the following will help.
1. Produce a table (green) of values from column A and row 1 using: =IF(D2="X",D$1&" "&$A2,"")
2. Produce a list (blue) from the new table using*: =IFERROR(INDIRECT(TEXT(SMALL(IF((T$2:AH$13<>""),ROW(A$2:A$13)*100+COLUMN(T$1:AH$1),10^10),ROWS(A$1:A1)),"R0C00"),)&"","")
3. Produce a final list (B15:B80) using: =IF(T15="","","HD "&T15)
*Denotes an array entered formula with is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
Note that both the green table and blue list may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.