Solved

Find MIN of Column in Excel Based on Value in Another Column

Posted on 2014-01-31
13
263 Views
Last Modified: 2014-02-04
I have a pretty simple spreadsheet where I have a key in one worksheet with employee initials and another worksheet with all employee system login times. The initials are my lookup on the login time worksheet.

I am trying to create a formula in the first worksheet that will look for the employee's initials in the second worksheet and return the earliest login time

When Sheet1!A2 = Sheet2!H:H
return MIN(Sheet2!C:C)

Thanks in advance...
0
Comment
Question by:sparker1970
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39825357
Do you have a sample file?

You can add a helper column to find the first login time, then use a vlookup to pull that information to your first page.
0
 

Author Comment

by:sparker1970
ID: 39825374
Here is a basic version of the spreadsheet.

In sheet1 column D I want the earliest time from column C on sheet 2 when the employee initials match to sheet2
HelperFile.xlsx
0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
ID: 39825564
This was a fun one!

I didn't realize you had two dates on Sheet 2, so I was wondering why my spot checks were coming up wrong.

So, what I have done is:
1) Changed Column C to an actual Time Format.

2) Sorted the data on Sheet 2:
     a) By Trans_Date - Oldest to Newest
     b) By User_Init - A to Z
     c) Log Time - A to Z

3) Formula in D2 on Sheet 1, using Ctrl + Shift + Enter

=IFERROR(1/MAX((A8=Sheet2!H$2:H$2011)*(1/Sheet2!C$2:C$2011)),"")

Open in new window



==============
What this basically does is:

Takes the initials on Sheet 1 and finds them on Sheet 2.
Finds the corresponding time.
Brings the value that is the earliest in the day to the D2.

Once I verified value, copy down to remaining cells (D3:D24).

I have highlighted the first few employees in Sheet 2 so you can verify the times versus what the formula displays.
HelperFile-EE-.xlsx
0
 
LVL 80

Expert Comment

by:byundt
ID: 39825706
If you have Excel 2010 or later, you can use the AGGREGATE function to find your earliest time. The AGGREGATE function does not require an array-entered formula. And as shown below, it can tolerate one or more blank cells in column C; the array-entered MAX formula blows up (returns an empty string) in such situations.
=IFERROR(AGGREGATE(15,6,Sheet2!C$2:C$2011/((Sheet2!C$2:C$2011<>"")*(A2=Sheet2!H$2:H$2011)),1),"")
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39829023
Another option:

Using the DMIN function with the following syntax, the criteria would be separate small table where you specify column Hedaer and criteria, eg in your case initials.

=DMIN(Database,Field,Criteria)

Thanks
Rob H
0
 

Author Comment

by:sparker1970
ID: 39829729
Trying some solutions this morning to see where things stand...

Rob - The DMIN does not work because I have multiple employees in the list. This was the first option I tried and no matter what I did it did not work with multiple criteria.

byundt - I tried yours a few different ways and I could not get it to work. I was hoping it did. Can you possibly use my helper file and re-post showing your solution?

ThinkSpace - I'm trying your solution now and will post results shortly.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Closing Comment

by:sparker1970
ID: 39829825
Do you think that since this required a sort that there is any way to modify the formula line to bring back the oldest time for an employee in the next column?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39829848
sparker1970,
If you got nothing when pasting my formula in Sheet1 cell D2, that's because the user with initials MXV wasn't found in Sheet2 column H.

If you got some funny looking numbers as you copied my formula down, that's because you need to reformat column D as time.

If you got a #NAME? error value, that's because you have Excel 2007 or earlier.
HelperFileQ28353696.xlsx
0
 

Author Comment

by:sparker1970
ID: 39829915
byundt,

I am using Excel 2007. I am getting nothing returned in the field. There is no error in the formula or in how I am typing it. Is there an add-on or other functionality I would need to turn on or activate for the IFERROR command to function properly?

Even if I use your spreadsheet and replicate the formula it does not work in the spreadsheet you uploaded even though I see yours working.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39830083
The IFERROR was hiding the #NAME? error values in Excel 2007.

In Excel 2007, you can use an array-entered formula:
=IFERROR(1/(1/MIN(IF((A2=Sheet2!H$2:H$2011)*(Sheet2!C$2:C$2011<>0),--Sheet2!C$2:C$2011,""))),"")

To array-enter a formula:
1.  Select the cell, then click in the formula bar
2.  Hold the Control and Shift keys down
3.  Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula.

In the attached workbook, I put the array-entered formula in worksheet Sheet1 column E.

In the above formula, MIN will return 0 if there are no data in column H that match A2. To trap that possibility and return an empty string instead, the formula calculates 1/MIN, which will return a #DIV/0! error value. To return a normal time when there is a match, the formula calculates 1/(1/MIN(....)).

The reason for the double hyphens in --Sheet2!C$2:C$2011 is to convert text that looks like a time into real time values.
HelperFileQ28353696.xlsx
0
 

Author Comment

by:sparker1970
ID: 39830416
byundt - Worked perfectly now. I was even able to modify it to give me the latest time in another column. I looked for a way to give shared credit since your solution worked and I was able to easily modify it for a secondary purpose but was not able to find it.

I will keep an eye out for your name in future posts (which will be shortly)
0
 

Author Comment

by:sparker1970
ID: 39833535
Will do. Still a rookie at this and I am learning the ins and outs but appreciate the feedback.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now