Max and Min date from listing by account no.
 

Hi,

I have a list of customer account numbers each with a sales value and date
e.g. columns A = AccNo, B = Sales and C = Date, there are many entries for
each account no.

In another sheet, I have the account number shown once and want to show
alongside this the min and max date with sale i.e. columns A = AccNo, B =
MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max).

Do I use and array formula?

Thanks, Rob

Hi Rob

yes, you can use the following array formuls to return the min & max dates
=MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
=MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
-- need to format these as dates

to return the min and max sales

=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))
where
-Sheet1!$B$1:$B$13 is the sales figures on your first sheet
-A2&B2 is the account number and min date on this sheet
-Sheet1!$A$1:$A$13 is the account number on your first sheet
-Sheet1!$C$1:$C$13 is the min date on your first sheet

the formula for max sales would be
=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))

all of these formulas are array formuls so enter using control & shift &
enter not just enter

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Rob" wrote in message
...
Hi,

I have a list of customer account numbers each with a sales value and date
e.g. columns A = AccNo, B = Sales and C = Date, there are many entries for
each account no.

In another sheet, I have the account number shown once and want to show
alongside this the min and max date with sale i.e. columns A = AccNo, B =
MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max).

Do I use and array formula?

Thanks, Rob

Thanks Julie for quick reply, I'll try this out now. Rob

"JulieD" wrote in message
...
Hi Rob

yes, you can use the following array formuls to return the min & max dates
=MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
=MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
-- need to format these as dates

to return the min and max sales

=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))
where
-Sheet1!$B$1:$B$13 is the sales figures on your first sheet
-A2&B2 is the account number and min date on this sheet
-Sheet1!$A$1:$A$13 is the account number on your first sheet
-Sheet1!$C$1:$C$13 is the min date on your first sheet

the formula for max sales would be
=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))

all of these formulas are array formuls so enter using control & shift &
enter not just enter

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Rob" wrote in message
...
Hi,

I have a list of customer account numbers each with a sales value and
date e.g. columns A = AccNo, B = Sales and C = Date, there are many
entries for each account no.

In another sheet, I have the account number shown once and want to show
alongside this the min and max date with sale i.e. columns A = AccNo, B
= MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max).

Do I use and array formula?

Thanks, Rob

let us know how you get on
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Rob" wrote in message
...
Thanks Julie for quick reply, I'll try this out now. Rob

"JulieD" wrote in message
...
Hi Rob

yes, you can use the following array formuls to return the min & max
dates
=MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
=MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
-- need to format these as dates

to return the min and max sales

=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))
where
-Sheet1!$B$1:$B$13 is the sales figures on your first sheet
-A2&B2 is the account number and min date on this sheet
-Sheet1!$A$1:$A$13 is the account number on your first sheet
-Sheet1!$C$1:$C$13 is the min date on your first sheet

the formula for max sales would be
=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))

all of these formulas are array formuls so enter using control & shift &
enter not just enter

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Rob" wrote in message
...
Hi,

I have a list of customer account numbers each with a sales value and
date e.g. columns A = AccNo, B = Sales and C = Date, there are many
entries for each account no.

In another sheet, I have the account number shown once and want to show
alongside this the min and max date with sale i.e. columns A = AccNo, B
= MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max).

Do I use and array formula?

Thanks, Rob

Thanks Julie for quick reply, I'll try this out now. Rob

"JulieD" wrote in message
...
Hi Rob

yes, you can use the following array formuls to return the min & max
dates
=MIN(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
=MAX(IF(Sheet1!A2:A13=A2,Sheet1!C2:C13,""))
-- need to format these as dates

to return the min and max sales

=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
B2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))
where
-Sheet1!$B$1:$B$13 is the sales figures on your first sheet
-A2&B2 is the account number and min date on this sheet
-Sheet1!$A$1:$A$13 is the account number on your first sheet
-Sheet1!$C$1:$C$13 is the min date on your first sheet

the formula for max sales would be
=INDEX(Sheet1!$B$1:$B$13,MATCH(A2&
D2,Sheet1!$A$1:$A$13&Sheet1!$C$1:$C$13,0))

all of these formulas are array formuls so enter using control & shift &
enter not just enter

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Rob" wrote in message
...
Hi,

I have a list of customer account numbers each with a sales value and
date e.g. columns A = AccNo, B = Sales and C = Date, there are many
entries for each account no.

In another sheet, I have the account number shown once and want to show
alongside this the min and max date with sale i.e. columns A = AccNo, B
= MinDate, C = Sale(Min), D = MaxDate and E = Sale(Max).

Do I use and array formula?

Thanks, Rob