|
Lookup Oldest date from previously selected Group Data
Sample Data (table)
A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
Hi Jim,
=MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13)) as an aray formula -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:dJQge.4203$It1.55@lakeread02... Sample Data (table) A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
Hi!
Try this: Array entered: =MIN(IF(Sheet1!A$1:A$13=D4,Sheet1!C$:C$13)) Format as DATE Biff "Jim May" wrote in message news:dJQge.4203$It1.55@lakeread02... Sample Data (table) A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
much appreciated Bob;
works great!! Jim "Bob Phillips" wrote in message ... Hi Jim, =MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13)) as an aray formula -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:dJQge.4203$It1.55@lakeread02... Sample Data (table) A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
Thanks Biff for the help!
Jim "Biff" wrote in message ... Hi! Try this: Array entered: =MIN(IF(Sheet1!A$1:A$13=D4,Sheet1!C$:C$13)) Format as DATE Biff "Jim May" wrote in message news:dJQge.4203$It1.55@lakeread02... Sample Data (table) A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
Bob:
The CSE formula worked perfect in my (Small and simple) example; but when I applied it against a huge set of data (1500 rows --for a fellow employee) it produced 01/00/00 (Date formatted) Value of 0. This probably due to perhaps some "foreign-crap" within the Ranges Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc. Can I test (On my Sheet1) using a temporary helper column to determine the culprit cell(s) causing my final CSE formula to produce the 0 value? Thanks for your help!! Jim "Bob Phillips" wrote in message ... Hi Jim, =MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13)) as an aray formula -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:dJQge.4203$It1.55@lakeread02... Sample Data (table) A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
Hi Jim,
Why not just filter the date column for a 0 date? -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:4rmhe.5436$It1.3577@lakeread02... Bob: The CSE formula worked perfect in my (Small and simple) example; but when I applied it against a huge set of data (1500 rows --for a fellow employee) it produced 01/00/00 (Date formatted) Value of 0. This probably due to perhaps some "foreign-crap" within the Ranges Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc. Can I test (On my Sheet1) using a temporary helper column to determine the culprit cell(s) causing my final CSE formula to produce the 0 value? Thanks for your help!! Jim "Bob Phillips" wrote in message ... Hi Jim, =MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13)) as an aray formula -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:dJQge.4203$It1.55@lakeread02... Sample Data (table) A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
In my sample range A1:A13 there was a single blank cell of Text
which was causing the problem - once I filled it in the original formula worked. I tried (to eliminate this possibility from happening again by trying the following but it didn't seem to help/work... =MIN(IF(AND(miss!$D$5:$D$1444<"",miss!$D$5:$D$144 4=$B$4),miss!$E$5:$E$1444) ) <<< where D:D column hold my VCode (one SMPC-Text) and E:E holds the dates.. Tks Bob, Jim May "Bob Phillips" wrote in message ... Hi Jim, Why not just filter the date column for a 0 date? -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:4rmhe.5436$It1.3577@lakeread02... Bob: The CSE formula worked perfect in my (Small and simple) example; but when I applied it against a huge set of data (1500 rows --for a fellow employee) it produced 01/00/00 (Date formatted) Value of 0. This probably due to perhaps some "foreign-crap" within the Ranges Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc. Can I test (On my Sheet1) using a temporary helper column to determine the culprit cell(s) causing my final CSE formula to produce the 0 value? Thanks for your help!! Jim "Bob Phillips" wrote in message ... Hi Jim, =MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13)) as an aray formula -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:dJQge.4203$It1.55@lakeread02... Sample Data (table) A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
Jim,
Is this what you want? =MIN(IF((miss!$D$5:$D$1444=$B$4)*(miss!$E$5:$E$144 4<""),miss!$E$5:$E$1444)) -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:2uqhe.5439$It1.2570@lakeread02... In my sample range A1:A13 there was a single blank cell of Text which was causing the problem - once I filled it in the original formula worked. I tried (to eliminate this possibility from happening again by trying the following but it didn't seem to help/work... =MIN(IF(AND(miss!$D$5:$D$1444<"",miss!$D$5:$D$144 4=$B$4),miss!$E$5:$E$1444) ) <<< where D:D column hold my VCode (one SMPC-Text) and E:E holds the dates.. Tks Bob, Jim May "Bob Phillips" wrote in message ... Hi Jim, Why not just filter the date column for a 0 date? -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:4rmhe.5436$It1.3577@lakeread02... Bob: The CSE formula worked perfect in my (Small and simple) example; but when I applied it against a huge set of data (1500 rows --for a fellow employee) it produced 01/00/00 (Date formatted) Value of 0. This probably due to perhaps some "foreign-crap" within the Ranges Sheet1!$A$1:$A$13, actually Sheet1!$D$5:$D$1500, etc, etc. Can I test (On my Sheet1) using a temporary helper column to determine the culprit cell(s) causing my final CSE formula to produce the 0 value? Thanks for your help!! Jim "Bob Phillips" wrote in message ... Hi Jim, =MAX(IF(Sheet1!$A$1:$A$13=Sheet2!$D$4,Sheet1!$C$1: $C$13)) as an aray formula -- HTH RP (remove nothere from the email address if mailing direct) "Jim May" wrote in message news:dJQge.4203$It1.55@lakeread02... Sample Data (table) A B C D 1 APM xxxxxxxxxxx 1/15/05 2.00 2 APM xxxxxxxxxxx 2/28/05 2.00 3 APM xxxxxxxxxxx 1/13/05 2.00 4 APM xxxxxxxxxxx 12/4/04 2.00 5 APM xxxxxxxxxxx 3/15/05 2.00 6 APM xxxxxxxxxxx 4/20/05 2.00 7 APM Total 12.00 8 GPS xxxxxxxxxxx 4/13/05 3.00 9 GPS xxxxxxxxxxx 4/10/05 3.00 10 GPS xxxxxxxxxxx 4/5/05 3.00 11 GPS xxxxxxxxxxx 12/15/04 3.00 12 GPS xxxxxxxxxxx 11/27/04 3.00 13 GPS xxxxxxxxxxx 3/10/05 3.00 14 GPS Total 18.00 Above on Sheet1 On my Sheet 2 Cell D4 = GPS Cell F4 = =SUMIF(Sheet1!$A$1:$A$13,sheet2!$D$4,Sheet1!$D$1:$ D$13) which displays 18 In Cell F5 i need to Bring back the oldest date in the same GPS group, the answer 11/27/04,,, Can someone help me? Tks in advance.. |
| All times are GMT +1. The time now is 12:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com