Max If Array

[Robby19]

Good day again,

I need to find the Max volunteered hours for each unit. I believe this is going to be an array, but could someone help me put it together.
Data Table
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Unit[/TD]
[TD]Employee[/TD]
[TD]Total Volunteer Hours[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]b[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]c[/TD]
[TD]35[/TD]
[/TR]
</TBODY>[/TABLE]

The result table should look like
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Unit[/TD]
[TD]Employee[/TD]
[TD]Total Volunteer Hours[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]b[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[TD]24[/TD]
[/TR]
</TBODY>[/TABLE]


I may also need to figure out if 2 or more people from the same unit have the same amount of hours. If that is the case the result should be
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Unit[/TD]
[TD]Employee[/TD]
[TD]Total Volunteer Hours[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]b, d[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[TD]24[/TD]
[/TR]
</TBODY>[/TABLE]

 

[wideboydixon]

Looking at your signature and on the assumption you're happy to use helper columns then something like this:

Book1
	A	B	C
1	Unit	Employee	Total Volunteer Hours
2	1	a	12
3	2	a	24
4	1	b	57
5	1	c	35
6	1	d	57
7	3	a	13
8	3	b	22
9	3	c	45
10	3	d	34
11	3	e	45
12	3	f	45
13	3	g	45
Sheet1

Book1
	A	B	C	D	E	F	G	H
1	Unit	Total Volunteer Hours	Employee
2	1	57	b,d	,b	,d
3	2	24	a	,a
4	3	45	c,e,f,g	,c	,e	,f	,g
Sheet2

Cell Formulas
Range	Formula
C2	=MID($D2&$E2&$F2&$G2&$H2,2,1024)
D2	{=IFERROR(","&INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A:$A=$A2,IF(Sheet1!$C:$C=$B2,ROW(Sheet1!$B:$B))),COLUMNS($D2:D2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula in D2 across to H2 and down as far as necessary.

WBD

 

[Robby19]

The above Array does not seem to work for me. I can get the MAX IF hours, however the name of the person for those hours does not match up. Below is my version for the spreadsheet.

{=IFERROR(INDEX(Hours[Full Name],SMALL(IF(Hours[Unit]=[@[Unit Name]],IF(Hours[Total Volunteer Hours]=[@[Total Hours]],ROW(Hours[Full Name]))),COLUMNS([Full Name]))),"")}

Not sure if I did something wrong or not.

EDIT: I removed the "," since I don't need to see the comma in the formula.

 

[wideboydixon]

That bears little resemblance to what you originally posted. Can you post your actual data?

WBD