Lookup Oldest date from previously selected Group Data

Jim May · 05-12-2005, 07:06 PM

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 Phillips · 05-12-2005, 07:06 PM

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" <[email protected]> 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..
>
>

Biff · 05-12-2005, 08:06 PM

Hi!

Try this:

Array entered:

=MIN(IF(Sheet1!A$1:A$13=D4,Sheet1!C$:C$13))

Format as DATE

Biff

"Jim May" <[email protected]> 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 May · 05-12-2005, 08:06 PM

much appreciated Bob;
works great!!
Jim

"Bob Phillips" <[email protected]> wrote in message
news:%23CGQ%[email protected]...
> 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" <[email protected]> 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 May · 05-12-2005, 10:07 PM

Thanks Biff for the help!
Jim

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try this:
>
> Array entered:
>
> =MIN(IF(Sheet1!A$1:A$13=D4,Sheet1!C$:C$13))
>
> Format as DATE
>
> Biff
>
> "Jim May" <[email protected]> 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 May · 05-14-2005, 09:07 AM

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" <[email protected]> wrote in message
news:%23CGQ%[email protected]...
> 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" <[email protected]> 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 Phillips · 05-14-2005, 12:06 PM

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" <[email protected]> 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" <[email protected]> wrote in message
> news:%23CGQ%[email protected]...
> > 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" <[email protected]> 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 May · 05-14-2005, 02:06 PM

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$1444=$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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
> > news:%23CGQ%[email protected]...
> > > 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" <[email protected]> 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 Phillips · 05-14-2005, 03:06 PM

Jim,

Is this what you want?

=MIN(IF((miss!$D$5:$D$1444=$B$4)*(miss!$E$5:$E$1444<>""),miss!$E$5:$E$1444))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Jim May" <[email protected]> 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$1444=$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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> 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" <[email protected]> wrote in message
> > > news:%23CGQ%[email protected]...
> > > > 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" <[email protected]> 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..
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>