First and Latest Tranascation Date

[Jim]

I have a spreasheet with sheets. The first sheet (Sheet1) is a list
of all tranasction from customers sorted by ID and then date (early to
late) (ie. multiple entries per cusotmer) ID = Row A date Row B The
second sheet (Sheet2) is a single entry for each customer ID. In this
second sheet I want to get the first and last tranasction dates
tranasctaion dates for each customet from Sheet1

 

[Bob Phillips]

Try these

=MAX(IF(Sheet1!A2:A200=Sheet2!A2,Sheet1!B2:B200))

=MIN(IF((Sheet1!B2:B200<>"")*(Sheet1!A2:A200=Sheet2!A2),Sheet1!B2:B200))

which are array formulae, and should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim]

Not sure this is correct

=MAX(IF(Sheet1!A2:A200=Sheet2!A2,Sheet1!B2:B200))

If the ID numbers are in Sheet1 Column A but the "Dates" are in Column
B of Sheet1 how does it get the maximum Dates the dates?

 

[Bob Phillips]

Try it and see.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim]

So i did and i could not get it to work. Hopwever i did modify and
this seems to work (also need to make sure formatting was identitcal)


=MAX(IF(Sheet1!A:A=Sheet2=TEXT(B2,"00000"),'Sheet2'!M:M))

which works but is incredibly slow

I sped it up a bit with

=MAX(IF(Sheet1!A1:A20000=Sheet2=TEXT(B2,"00000"),'Sheet2'!B1:B2000))

but I still need autocal off

=MAX(IF(Sheet1!A2:A200=Sheet2!A2,Sheet1!B2:B200))

and this On Thu, 21 May 2009 12:18:04 +0100, "Bob Phillips"

Try it and see.

=MIN(IF('Procdures Cleaned'!K1:K23000=TEXT(B1501,"00000"),'Procdures
Cleaned'!M1:M23000))

 

[Bob Phillips]

You should change those text values on Sheet1 to real numbers, remove the
TEXT function from the formula, and it will be quicker.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)