We help IT Professionals succeed at work.

# Find minimum based on grouped criteria

on
Hi,

I am having trouble figuring out how to do this!

I have the following

A       22
A       55
A       23
B       4
B       98
C       0
D       1
D       78
D       2
D       66
D       93
etc

On the column C, I want to populate mimums based on the criteria on column A

eg:

A - the min is 22
B - 4
etc!

Any help is much appreciated!

Thanks!
Comment
Watch Question

## View Solutions Only

Top Expert 2008
Commented:
The easiest would be to create a pivot table, summarizing your data with the minimum function. See attached file.
pivot-example.xlsx
CERTIFIED EXPERT

Commented:
Try an array formula

=MIN(IF(\$A\$1:\$A\$11=H22,\$B\$1:\$B\$11))

H22 is referring to a cell containing A. You can change it to your own cell

Commented:
Ok my mistake, I think I missed a point there in the question. Your answer is helping me but I also want to include this info

ALEX      08/14/12 5:27
ALEX      08/14/12 15:46
ALEX      08/08/12 14:33
ALEX      08/08/12 12:58
ALEX      08/08/12 11:33
CHARA      08/10/12 10:21
CHARA      08/10/12 8:01
CHARA      08/09/12 17:03
CHARA      08/09/12 16:18
DARBA      08/08/12 15:58
DARBA      08/08/12 14:48
DARBA      08/08/12 11:39
DARBA      08/07/12 19:50
DARBA      08/07/12 19:18
DARBA      08/07/12 14:13
DARBA      08/07/12 12:54

^ I actually have the above info

I want to find out whats Alex's maximum time on each specific date he worked (maximum time = time he exits the workplace)

Eg: Aug 14 -> 15:46
Aug 08 -> 14:33
Chara
Augu 10 -> 10:21, etc

Thanks!
Top Expert 2008

Commented:
Then all the more reason to do a pivot, Name and date as row fields, minimum of time as the data field.

Check this question for recommendations on pivot table tutorials, or try some of the following:

http://office.microsoft.com/en-us/excel-help/create-or-delete-a-pivottable-or-pivotchart-report-HP010342375.aspx?CTT=3
http://www.dummies.com/how-to/content/the-essentials-of-excel-2010-pivot-tables-and-pivo.html
http://msdn.microsoft.com/en-us/library/office/aa164458%28v=office.10%29.aspx

Thomas
CERTIFIED EXPERT
Commented:
To get the maximum you can also use

=MIN(IF(\$A\$1:\$A\$11=H22,\$B\$1:\$B\$11))

You must press shift-ctrl-enter to enter the formula.
Finance Analyst
CERTIFIED EXPERT
Commented:
Shanan212

If you were to split the date and time into separate columns, you would be able to apply a pivot table as suggested by nutsch.

Assuming data in columns A & B, lets set up copy data for the pivot in columns E to G:

Column E  =A1
Column F  =INT(B1)       format as date
Column G =B1-INT(B1)  format as time

Then use columns E to G for the pivot with name and date as row labels and time as data field with minimum set.

Thanks
Rob H

Commented:
Thanks all!