Populating row headers based on column values

[Jonam711]

Hey guys - I'm working on a daily report were 20 employees update their daily data in sheet 1 following are the column headers in sheet 1"status" drop down list - pending, processed, on hold. the sheet has other columns such as Date when assigned, completed date, no of days pending or on hold. By the end of the day one has to publish the pending/on hold count of each employees based on days range as shown in the attached image (sheet 2). The formulas I put in sheet 2 are working fine. But how it's done now is I have made a list of all the 20 employees and I used "SORT" for that list so all the 20 emps names comes up and we manually delete the rows of employees who don't have any pending counts in any of the days bucket. If you see out of 20 employees only let's say 7 employees have pending/on hold under their names and I need to populate only those 7 employees in a column and show their pending counts.

 

[RobP]

did you forget to attach something ?
Rob

 

[Jonam711]

Hey guys - I'm working on a daily report were 20 employees update their daily data in sheet 1 following are the column headers in sheet 1"status" drop down list - pending, processed, on hold. the sheet has other columns such as Date when assigned, completed date, no of days pending or on hold. By the end of the day one has to publish the pending/on hold count of each employees based on days range as shown in the attached image (sheet 2). The formulas I put in sheet 2 are working fine. But how it's done now is I have made a list of all the 20 employees and I used "SORT" for that list so all the 20 emps names comes up and we manually delete the rows of employees who don't have any pending counts in any of the days bucket. If you see out of 20 employees only let's say 7 employees have pending/on hold under their names and I need to populate only those 7 employees in a column and show their pending counts.

 

[Jonam711]

did you forget to attach something ?
Rob

Oops I forgot, I've attached the images as a reply to my post

 

[RobP]

Sorry, but I am not able to correlate what you are saying above in the text, to what you are posting in the pictures .. so its very difficult to understand what you are trying to achieve. Others might be able to help, or better, perhaps take some more time to really share details and perhaps examples of what you are asking for.

That should help others also understand and perhaps get you a reply.

cheers
Rob

 

[Jonam711]

Sorry, but I am not able to correlate what you are saying above in the text, to what you are posting in the pictures .. so its very difficult to understand what you are trying to achieve. Others might be able to help, or better, perhaps take some more time to really share details and perhaps examples of what you are asking for.

That should help others also understand and perhaps get you a reply.

cheers
Rob

 

[Jonam711]

Sorry, let me put in another way. I have made list of 5 associates in a different sheet and I'd sort of those associates list in column B in sheet1 as you see in image. Associates in B20 and B21 do not have any values in their respective cells C20:G20 and C21:G21. so I do not want them to be populated here instead I just want associates who has values in any of their respective row cells starting C18:G22. Hope I made it clear.

 

[RobP]

Hi,

So as far as I understand you are looking to create a report somewhere else to show only the associates that have values in your table c18:g22.

Maybe this formula I have shown in B25 can help you - let me know.

cheers
Rob

football scores.xlsx
	A	B	C	D	E	F	G
16
17		Buying Associates	Day 1-3	Day 3-5	Day 6-10	Day 11-15	Day 15+
18		Bill Gates	1	0	3	2	0
19		Dave Bruns	1	0	1	0	0
20		Steve Jobs	0	0	0	0	0
21		Don Trump	0	0	0	0	0
22		Boris Johnson	1	0	0	1	1
23
24
25		Bill Gates	1	0	3	2	0
26		Dave Bruns	1	0	1	0	0
27		Boris Johnson	1	0	0	1	1
Sheet2

Cell Formulas
Range	Formula
B25:G27	B25	=FILTER(B18:G22,BYROW(C18:G22,LAMBDA(row,SUM(row)))>0)
Dynamic array formulas.

 

[Jonam711]

Thank you that worked. However its displaying only the associates names who has value, can I also get their values.

 

[RobP]

Hi,

maybe I didn't understand what you are asking.. You asked for the names and values of those who had total sum >0 in your table C18:G22.

So the formula I have placed in B25 provides the names, and values of all those in the table that are total>0. The values are shown after the names ?

what did I miss here ?

thanks
Rob