how to locate a certain value within a table and extract row and column information?

Question

Hello Stack Overflow Folks. While i'm working on my project, I encountered an excel question.

if you see the chart attached above, the big table at the top is made of basically three information. first column having the value from 4~25 is the number of floors. and the first raw having value of 01~36 is the number of rooms on each floor

and k1, k2, d1, d2 etc are showing room types for each room. for example, room 401 is K4 room and room 502 is K5 room.

What I'm trying to make is generating a table at the bottom showing what rooms are having D1, D1.1, D2.

The final result would be like

D1 - 411,412,413.... D2 - 415,416,417,....

Does anyone know what function could be used for this? I tried Vlookup and Hlookup but it didn't seem like it's working for this task.

Answer 1

Soonk,

Please follow this steps for the PowerQuery solution:

Make a copy of your workbook before apply any changes!

Convert the data with the floors, rooms and room types to an Excel (structured table)
1. Select the data range
2. Click on the Ribbon "Data" | "From table/range" (check: My table has headers)

--PowerQuery editor shows--
3. Select columns 01 to 36 by clicking the headers (you can select 01 and then press shift on the keyboard and then click on 36)
4. Click on the Ribbon "Transform" | "UnPivot columns"
5. Select columns "Column1" and "Attribute" by clicking the headers
6. Click on the Ribbon "Add Column" | "Merge columns" | "Ok"
7. Select column "Value"
8. Click on the Ribbon "Transform" | "Group By"
9. Change "New column name" to AllData
10. Change "Operation" to "AllRows"
11. Click "Ok"
12. Click on the Ribbon "Add Column" | "Custom Column"
13. Enter the folloing formula inside the "Custom column formula box": [AllData][Merged]
14. Click "Ok"
15. Click on the two arrows button in the "Custom" column header
16. Select "Extract values..."
17. Select the "Comma" delimiter
18. Right click the "AllData" column and select "Remove"
19. Click on the Ribbon "Home" | "Close and load"
20. Select the location of the result and click Ok

Result should load to the Excel spreadsheet.

Here is a screencast of the steps:

---