|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Ok, first post and pretty much a new user to Excel. I have two sheets
that I am working with, trying to recall data from one to the other
that meets criteria. In a nutshell:
Sheet 1 contains a column of unique values (col A), cols C,D, and E are
where I want to insert the formula to find data on Sheet 2.
Sheet 2 contains 4 columns, A contains multiple occurrences of each
value (from Sheet1, column A), each with its own timestamp in column
D.
On Sheet1, in column C, I want to find a value on Sheet2 in column A
and return the timestamp in column D. I know I can use VLOOKUP for
this.
=VLOOKUP(B2,'Sheet2'!,A:A,4)
WHat I need to do is:
For Sheet1, column C:
Find the value (on Sheet2) with a timestamp in between minute 00 and
minute 19
For Sheet1, column D:
Find the value on Sheet2 with a timestamp between minute 20 and minute
39
For Sheet1, column E:
Find the value on Sheet2 with a timestamp between minute 40 and minute
59
Ok, it may not even make sense, but as I said, learning Excel here so a
lot of this is completely foreign to me.
--
snobordr
------------------------------------------------------------------------
snobordr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36075
View this thread: http://www.excelforum.com/showthread.php?threadid=558617
|
|
=INDEX(Sheet2!$D$2:$D$20,MATCH(1,(Sheet1!$A2=Sheet2!$A$2:$A$20)*
(MINUTE(Sheet2!$D$2:$D$20)>=0)*(MINUTE(Sheet2!$D$2:$D$20)<20),0))
=INDEX(Sheet2!$D$2:$D$20,MATCH(1,(Sheet1!$A2=Sheet2!$A$2:$A$20)*
(MINUTE(Sheet2!$D$2:$D$20)>=20)*(MINUTE(Sheet2!$D$2:$D$20)<40),0))
=INDEX(Sheet2!$D$2:$D$20,MATCH(1,(Sheet1!$A2=Sheet2!$A$2:$A$20)*
(MINUTE(Sheet2!$D$2:$D$20)>=40)*(MINUTE(Sheet2!$D$2:$D$20)<=60),0))
which are array formulae, it should be committed with Ctrl-Shift-Enter, not
just Enter.
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"snobordr" <snobordr.2ahmco_1152128410.442[ at ]excelforum-nospam.com> wrote in
message news:snobordr.2ahmco_1152128410.442[ at ]excelforum-nospam.com...
[Quoted Text] >
> Ok, first post and pretty much a new user to Excel. I have two sheets
> that I am working with, trying to recall data from one to the other
> that meets criteria. In a nutshell:
>
> Sheet 1 contains a column of unique values (col A), cols C,D, and E are
> where I want to insert the formula to find data on Sheet 2.
>
> Sheet 2 contains 4 columns, A contains multiple occurrences of each
> value (from Sheet1, column A), each with its own timestamp in column
> D.
>
> On Sheet1, in column C, I want to find a value on Sheet2 in column A
> and return the timestamp in column D. I know I can use VLOOKUP for
> this.
>
> =VLOOKUP(B2,'Sheet2'!,A:A,4)
>
> WHat I need to do is:
>
> For Sheet1, column C:
> Find the value (on Sheet2) with a timestamp in between minute 00 and
> minute 19
>
> For Sheet1, column D:
> Find the value on Sheet2 with a timestamp between minute 20 and minute
> 39
>
> For Sheet1, column E:
> Find the value on Sheet2 with a timestamp between minute 40 and minute
> 59
>
>
>
> Ok, it may not even make sense, but as I said, learning Excel here so a
> lot of this is completely foreign to me.
>
>
> --
> snobordr
> ------------------------------------------------------------------------
> snobordr's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36075
> View this thread: http://www.excelforum.com/showthread.php?threadid=558617
>
|
|
Ok, thanks for the help and I get the concept now. What I don't get are
the results that I should from using these formula. The column I am
referencing for the time filter is retrieved from a MS SQL database and
comes in their datetime format. The minute ><= functionality is not
working correctly and I am wondering if this is due to the format from
SQL.
Thanks again. ;)
--
snobordr
------------------------------------------------------------------------
snobordr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36075
View this thread: http://www.excelforum.com/showthread.php?threadid=558617
|
|
Can you give an example.
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"snobordr" <snobordr.2ajgsc_1152214505.0821[ at ]excelforum-nospam.com> wrote in
message news:snobordr.2ajgsc_1152214505.0821[ at ]excelforum-nospam.com...
[Quoted Text] >
> Ok, thanks for the help and I get the concept now. What I don't get are
> the results that I should from using these formula. The column I am
> referencing for the time filter is retrieved from a MS SQL database and
> comes in their datetime format. The minute ><= functionality is not
> working correctly and I am wondering if this is due to the format from
> SQL.
>
> Thanks again. ;)
>
>
> --
> snobordr
> ------------------------------------------------------------------------
> snobordr's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36075
> View this thread: http://www.excelforum.com/showthread.php?threadid=558617
>
|
|
|