Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array using MIN
Hello -
Im trying to pull an employees start ang end logouts from a timecard text file that is being converted into excel. Sheet 1 looks like the following: A B C 1 ID Start End 2 30008 11:06 AM 12:47 PM 3 30008 1:16 PM 2:46 PM 4 30008 3:01 PM 4:23 PM 5 30015 7:54 AM 9:12 AM 6 30015 9:20 AM 9:53 AM 7 30015 10:10 AM 11:40 AM 8... Sheet 2 column A has all the ID's in it and then Column B is where I want to list their times. Yes - easy way is vlookup, but I'm trying to avoid that due to another reason. What I thought would work is the following formula... but I'm wrongo - my greatest thanks for any suggestions in advance. {=if($A1=Sheet1!$A$2:$A$500,min($B$2:$B$500),""))} |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array using MIN
Details on what you want to do are vague, but guessing from your formula, try:
=min(if(Sheet1!$A$2:$A$500=$A1,$B$2:$B$500)) array entered w/Cntrl+Shift+Enter "Jatiro" wrote: Hello - Im trying to pull an employees start ang end logouts from a timecard text file that is being converted into excel. Sheet 1 looks like the following: A B C 1 ID Start End 2 30008 11:06 AM 12:47 PM 3 30008 1:16 PM 2:46 PM 4 30008 3:01 PM 4:23 PM 5 30015 7:54 AM 9:12 AM 6 30015 9:20 AM 9:53 AM 7 30015 10:10 AM 11:40 AM 8... Sheet 2 column A has all the ID's in it and then Column B is where I want to list their times. Yes - easy way is vlookup, but I'm trying to avoid that due to another reason. What I thought would work is the following formula... but I'm wrongo - my greatest thanks for any suggestions in advance. {=if($A1=Sheet1!$A$2:$A$500,min($B$2:$B$500),""))} |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array using MIN
Try this:
=MIN(IF($A1=Sheet1!$A$2:$A$500,$B$2:$B$500)) ctrl+shift+enter, not just enter "Jatiro" wrote: Hello - Im trying to pull an employees start ang end logouts from a timecard text file that is being converted into excel. Sheet 1 looks like the following: A B C 1 ID Start End 2 30008 11:06 AM 12:47 PM 3 30008 1:16 PM 2:46 PM 4 30008 3:01 PM 4:23 PM 5 30015 7:54 AM 9:12 AM 6 30015 9:20 AM 9:53 AM 7 30015 10:10 AM 11:40 AM 8... Sheet 2 column A has all the ID's in it and then Column B is where I want to list their times. Yes - easy way is vlookup, but I'm trying to avoid that due to another reason. What I thought would work is the following formula... but I'm wrongo - my greatest thanks for any suggestions in advance. {=if($A1=Sheet1!$A$2:$A$500,min($B$2:$B$500),""))} |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array using MIN
Thank you both - Tetthless and JMB - quick response and accurate info - works
like tomato soup... hehe - It's late, I'm tired. Thanks again! "Jatiro" wrote: Hello - Im trying to pull an employees start ang end logouts from a timecard text file that is being converted into excel. Sheet 1 looks like the following: A B C 1 ID Start End 2 30008 11:06 AM 12:47 PM 3 30008 1:16 PM 2:46 PM 4 30008 3:01 PM 4:23 PM 5 30015 7:54 AM 9:12 AM 6 30015 9:20 AM 9:53 AM 7 30015 10:10 AM 11:40 AM 8... Sheet 2 column A has all the ID's in it and then Column B is where I want to list their times. Yes - easy way is vlookup, but I'm trying to avoid that due to another reason. What I thought would work is the following formula... but I'm wrongo - my greatest thanks for any suggestions in advance. {=if($A1=Sheet1!$A$2:$A$500,min($B$2:$B$500),""))} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of Offset function in array formula | Excel Worksheet Functions | |||
Display an array of references | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |