Hi All,
Thx for viewing my post!
Here's my situation, I hope you can help.
I have two sheets, one is a main database, the other a list I have shortened (The database has multiple instances (with varying information) for one record).
Database looks kind of like this:
A__________B (columns)
_#_______ time
251_____11:51 am
251_____12:01 pm
265_____ 2:52 am
265_____ 3:52 am
265_____ 4:35 pm
(notice the multiple instances of one # in the column a, but different corresponding times in column B
The short list looks kind of like this:
A_______B (columns)
_#_____time[/U]
251
265
(notice there is only one instance of each #, and no corresponding time)
Okay so here's what I need:
I need to get the earliest time listed on the database for each corresponding # on the short list.
so, for 251, I should get: 11:51 am
and for 265, I should get: 2:52 am
Please help if you can.....
Thank you very much; it is greatly appreciated!!
Last edited by mitelkm; 03-10-2010 at 10:50 AM.
=Vlookup(A1,Sheet2!$A$1:$B$200,2,0). Adjust ranges as necessary. This assumes the times are listed in chronological order.
With this layout:
A B C D E F 1 251 11:51 AM 251 11:51 AM 2 251 12:01 PM 265 2:52 AM 3 265 2:52 AM 4 265 3:52 AM 5 265 4:35 PM
The array formula in F1 is:
=MIN(IF($A$1:$A$5=E1,$B$1:$B$5))
...confirmed by pressing CTRL-SHIFT-ENTER.
When the first value appears properly, copy that cell down for the other codes.
NOTE: If your values are indeed always in chronological order, use the suggestion above...much better to avoid the array if you can.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank you so much!!!! it works!!!!! You are awesome!
One more thing, however, if you do not mind...
How can I get the formula to exclude blanks as a minimum? unless there is no higher criteria?
How would blank entries make it into your dataset? I would figure that out and resolve the issue there, avoid letting the bogus entries into the data in the first place.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi All,
Someone recently helped me out with this and I am hoping someone can help me take it one step further....
I am using a min code to find the lowest time entry of a certain record #
With this layout:
Code:
A B C D E F 1 251 11:51 AM 251 11:51 AM 2 251 12:01 PM 265 2:52 AM 3 265 2:52 AM 4 265 3:52 AM 5 265 4:35 PM
Here is the code that I was given: (What can I add to it so that if a min is zero, it will omit it and take the next minimum value?)
=MIN(IF($A$1:$A$5=E1,$B$1:$B$5))
Thanks so much!!!!!
Hi JBeaucaire,
It's an end user issue. If the time isn't entered by the person accessing the record it will come out blank. .... Time after time it happens...
Replace the array formula with this:
=MIN(IF(($A$1:$A$10=E1) * ($B$1:$B$10>0), $B$1:$B$10))
...also confirmed with CTRL-SHIFT-ENTER.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks