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

[Remya Ramakrishnan]

                                    

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

	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						13			15
Project B		29		35							25
Project C			12		16	11	17	18

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 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.

[#2]

Assumption:

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

Steps:

1. In Name manager, create a name, ProjectData

   =Sheet1!$A$2:$A$501

2. In Name manager, create a name, ResourceData

   =Sheet1!$B$2:$AY$501

3. In Name manager, create a name, ResourceHeaders

   =Sheet1!$B$1:$AY$1

4. In Name manager, create a name, SelectedProject

   =Sheet2!$A$1

5. In Name manager, create a name, ProjectResourceRow

   =INDEX(ResourceData, MATCH(SelectedProject,ProjectData,0),0)

6. In Name manager, create a name, ColumnList

   =IF(--(ProjectResourceRow<>0)=0,NA(),--(ProjectResourceRow<>0)*(COLUMN(ProjectResourceRow)-COLUMN(Sheet1!$A:$A)))

7. In Sheet2, cell A3

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

8. In Sheet2, cell B3
   

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

9.  Copy Sheet2!A3:B3, and paste the formula to Sheet2!A4:B53.

Try it.

[#3]

Hello Willy Lau,

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:

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

Steps:

1. In Name manager, create a name, ProjectData

   =Sheet1!$A$2:$A$501

2. In Name manager, create a name, ResourceData

   =Sheet1!$B$2:$AY$501

3. In Name manager, create a name, ResourceHeaders

   =Sheet1!$B$1:$AY$1

4. In Name manager, create a name, SelectedProject

   =Sheet2!$A$1

5. In Name manager, create a name, ProjectResourceRow

   =INDEX(ResourceData, MATCH(SelectedProject,ProjectData,0),0)

6. In Name manager, create a name, ColumnList

   =IF(--(ProjectResourceRow<>0)=0,NA(),--(ProjectResourceRow<>0)*(COLUMN(ProjectResourceRow)-COLUMN(Sheet1!$A:$A)))

7. In Sheet2, cell A3

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

8. In Sheet2, cell B3
   

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

9.  Copy Sheet2!A3:B3, and paste the formula to Sheet2!A4:B53.

Try it.

---

worked for me. Thank you once again.

[#4]

In fact, I am waiting the community members to update me there is a better solution for this topic (easier to accomplish the goal/better performance).  They are really great.