How to retrieve column header for non empty zero values as rows in a seperate sheet?

Question

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

I&nbsp;&nbsp;have been playing around with a problem for a few days, if you could help me to derive a solution, it would be great!

&nbsp;
Resource 1
Resource 2
Resource 3
Resource 4
Resource 5
Resource 6
Resource 7
Resource 8
Resource 9
Resource 10
Resource 11

Project A
10
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
13
&nbsp;
&nbsp;
15
&nbsp;

Project B
&nbsp;
29
&nbsp;
35
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
25

Project C
&nbsp;
&nbsp;
12
&nbsp;
16
11
17
18
&nbsp;
&nbsp;
&nbsp;

This is a table with resource allocation data.

In a separate sheet, if I select the project, the resource names(Resource 1, Resource 2 and the like) should come&nbsp;in rows.The challenge is to have the resource names where ever the cells are non-empty. Resource names with empty values are not needed.
Eg: If I select Project A, Resource 1, Resource 7 and Resource 10 shall come as rows just below the project number.

I have been trying to do with offset but I couldn't reach a conclusion.The data sheet is huge with 500+projects and resource allocation for 50+ resources
A response will be highly appreciated. It would be great if there is a solution without power query, as I don't have any know how on Macros.

willy lau · Answer

Assumption:

your table on A1:AY501 in sheet1, where project column on column a, and resource headers from B1 to AY1.
project selection cell on A1 in sheet2
resource listing from A3:B53. Its size matches with total number of resource
resource with empty cell or 0(zero) will not be listed

&nbsp;
Steps:

In Name manager, create a name, ProjectData
=Sheet1!$A$2:$A$501

In Name manager, create a name, ResourceData
=Sheet1!$B$2:$AY$501

In Name manager, create a name, ResourceHeaders
=Sheet1!$B$1:$AY$1

In Name manager, create a name, SelectedProject
=Sheet2!$A$1

In Name manager, create a name, ProjectResourceRow
=INDEX(ResourceData, MATCH(SelectedProject,ProjectData,0),0)

In Name manager, create a name, ColumnList
=IF(--(ProjectResourceRow&lt;&gt;0)=0,NA(),--(ProjectResourceRow&lt;&gt;0)*(COLUMN(ProjectResourceRow)-COLUMN(Sheet1!$A:$A)))

In Sheet2, cell A3
=IFERROR(INDEX(ResourceHeaders, 1, AGGREGATE(15,3,ColumnList,ROW(Sheet2!A3)-ROW(Sheet2!$A$2))),"")

In Sheet2, cell B3
=IFERROR(INDEX(ProjectResourceRow, 1, AGGREGATE(15,3,ColumnList,ROW(Sheet2!A3)-ROW(Sheet2!$A$2))),"")

&nbsp;Copy Sheet2!A3:B3, and paste the formula to Sheet2!A4:B53.

&nbsp;
Try it.

remya ramakrishnan · Answer

Hello Willy Lau,
&nbsp;
Thank you for the explanation, I could solve my problem using this formula too.
Really overwhelmed by the response I got from this community, and hats off to all your logic, most of the solutions suggested by community members
Willy Lau wrote:
Assumption: