Listing Non-Zero Values and their Headings

[scarrott]

I'm trying to put together a system to quote on jobs. One part of the quoter is showing how much labour is required depending on the jobs selected. I need to make a list of all the labourers required for the selected jobs, and the number of hours work required for each in total, ignoring any that have a total of 0 work. Like the following example.

Example.xlsx
	A	B	C	D	E	F
1	Job	Plasterer	Electrician	Joiner	Decorator	Plumber
2	Add Spotlights	1	3
3	Skim Wall	4			2
4	Extractor fan		5			1
5	Tile Walls					9
6	Replace Bath				1	4
7	Total	5	8	0	3	14
8
9
10
11	Work Required
12	Plasterer	5
13	Electrician	8
14	Decorator	3
15	Plumber	14
Sheet1

Cell Formulas
Range	Formula
B7:F7	B7	=SUM(B2:B6)

Can anyone help with a formula for the Work Required section?

 

[ebea]

Something like this ?

Mappe1
	A	B	C	D	E	F
1	Job	Plasterer	Electrician	Joiner	Decorator	Plumber
2	Add Spotlights	1	3
3	Skim Wall	4			2
4	Extractor fan		5			1
5	Tile Walls					9
6	Replace Bath				1	4
7	Total	5	8	0	3	14
8
9
10
11	Work Required
12	Plasterer	5
13	Electrician	8
14	Decorator	3
15	Plumber	14
Ark1

Cell Formulas
Range	Formula
B7:F7	B7	=SUM(B2:B6)
B12:B15	B12	=SUMPRODUCT($B$2:$F$6*(($B$1:$F$1=A12)))

 

[scarrott]

Thanks for the reply. Sorry, but I don't think I made it clear. I need the formula to pull in the headings too. So, in cells A12 down it would need to pull in the headings which have a value not equal to zero, and in B12 down it would need to pull in the totals for those headings. Basically, I need to make a nice list of labourers and the time they are needed for on a separate sheet. Hope that makes sense!

 

[Fluff]

Hi & welcome to MrExcel.
Another option

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Job	Plasterer	Electrician	Joiner	Decorator	Plumber
2	Add Spotlights	1	3
3	Skim Wall	4			2
4	Extractor fan		5			1
5	Tile Walls					9
6	Replace Bath				1	4
7	Total	5	8	0	3	14
8
9
10
11	Work Required
12	Plasterer	5
13	Electrician	8
14	Decorator	3
15	Plumber	14
16
Main

Cell Formulas
Range	Formula
B7:F7	B7	=SUM(B2:B6)
A12:B15	A12	=LET(m,MMULT(SEQUENCE(,5,,0),--(B2:F6)),TRANSPOSE(FILTER(CHOOSE({1;2},B1:F1,m),m>0)))
Dynamic array formulas.

 

[scarrott]

That looks great Fluff, just the ticket, thanks.

I assume there is no easy way to do this in older versions of Excel? I'm making this for someone else and I think they are on an older version.

 

[Fluff]

You could try

=IFERROR(INDEX($B$1:$F$1,AGGREGATE(15,6,(COLUMN($B$1:$F$1)-COLUMN($B$1)+1)/(MMULT(COLUMN($A:$E)^0,--($B$2:$F$6))>0),ROWS(A$12:A12))),"")

for the trades.

 

[scarrott]

Amazing. I've done that and used a simple HLOOKUP to get the totals for each. Thanks so much for the help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ebea]

Amazing. I've done that and used a simple HLOOKUP to get the totals for each. Thanks so much for the help!

I can see you already got a working solution. For older versions, another way are, in A12, and down.
{=IF($B12="";"";INDEX($B$1:$F$1;SMALL(IF($B$7:$F$7=$B12;COLUMN($B$1:$F$1)-COLUMN(B$2)+1);COUNTIF($B$12:B12;B12))))}

 

[Fluff]

Never thought of using the total, so my formula can be simplified to

=IFERROR(INDEX($B$1:$F$1,AGGREGATE(15,6,(COLUMN($B$1:$F$1)-COLUMN($B$1)+1)/($B$7:$F$7>0),ROWS(A$12:A12))),"")