Max Value of conditional list
 

I have a list of machines (MACHINE LIST) and another list of dates (DATE
LIST). The DATE list has the dates that each machine was used . The DATE
list is much longer than the MACHINE list because each machine has been used
multiple times on different dates.

I want to make a report that shows the last date each machine was used. I
could accomplish this by sorting the DATE list and then manually comparing
the two lists. But, I'd have to do that every time they were updated. I
want to automate the process.

Is there a formula that would give me the max date from the DATE list for
each entry on the MACHINE list.....something like a "MAXIF"

Thanks

Max Value of conditional list
 

With no knowledge of your sheet's actual layout, you'll have to interpret this.

A1-A6 = Machine1,Machine2,Machine3, etc....
B1-B6 = the last date the machine was used (we'll come back to this)

C1-C100 = machine names
D1-D100 = dates the machines in "c" were used

In B1, enter this array formula:

=MAX(IF($C$1:$C$100=A1,$D$1:$D$100,""))

....confirmed with CTRL-SHIFT-ENTER. Curly braces { } will appear around your
formula and the last date for Machine1 will appear. Now copy that cell down
through B6.


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"jkiser" wrote:

I have a list of machines (MACHINE LIST) and another list of dates (DATE
LIST). The DATE list has the dates that each machine was used . The DATE
list is much longer than the MACHINE list because each machine has been used
multiple times on different dates.

I want to make a report that shows the last date each machine was used. I
could accomplish this by sorting the DATE list and then manually comparing
the two lists. But, I'd have to do that every time they were updated. I
want to automate the process.

Is there a formula that would give me the max date from the DATE list for
each entry on the MACHINE list.....something like a "MAXIF"

Thanks

Max Value of conditional list
 

Suppose I have the machin list from Col A starting from row 1 and the dates
in ColB.

Cell C1 = Machine 1
In D1 enter the below formula. If the machine is not found it will return
"NOT USED"

=IF(COUNTIF(A$1:A1$00,D1)0,SUMPRODUCT(MAX(($A$1:$ A$100=D1)*($B$1:$B$100))),"NOT USED")


If this post helps click Yes
---------------
Jacob Skaria


"jkiser" wrote:

I have a list of machines (MACHINE LIST) and another list of dates (DATE
LIST). The DATE list has the dates that each machine was used . The DATE
list is much longer than the MACHINE list because each machine has been used
multiple times on different dates.

I want to make a report that shows the last date each machine was used. I
could accomplish this by sorting the DATE list and then manually comparing
the two lists. But, I'd have to do that every time they were updated. I
want to automate the process.

Is there a formula that would give me the max date from the DATE list for
each entry on the MACHINE list.....something like a "MAXIF"

Thanks

Max Value of conditional list
 

How can the date list be longer than the machine list?

It would have to be like this:

Machine1...date
...................date
...................date
Machine2...date
...................date
...................date
...................date

If you can fill in the blanks so that you have this:

Machine1...date
Machine1...date
Machine1...date
Machine2...date
Machine2...date
Machine2...date
Machine2...date

Then it would be fairly easy to get the max date. It's also fairly easy to
fill in the blanks if you need to.


--
Biff
Microsoft Excel MVP


"jkiser" wrote in message
...
I have a list of machines (MACHINE LIST) and another list of dates (DATE
LIST). The DATE list has the dates that each machine was used . The DATE
list is much longer than the MACHINE list because each machine has been
used
multiple times on different dates.

I want to make a report that shows the last date each machine was used. I
could accomplish this by sorting the DATE list and then manually comparing
the two lists. But, I'd have to do that every time they were updated. I
want to automate the process.

Is there a formula that would give me the max date from the DATE list for
each entry on the MACHINE list.....something like a "MAXIF"

Thanks

Max Value of conditional list
 

Forgot to mention to format D1 as Date.

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Suppose I have the machin list from Col A starting from row 1 and the dates
in ColB.

Cell C1 = Machine 1
In D1 enter the below formula. If the machine is not found it will return
"NOT USED"

=IF(COUNTIF(A$1:A1$00,D1)0,SUMPRODUCT(MAX(($A$1:$ A$100=D1)*($B$1:$B$100))),"NOT USED")


If this post helps click Yes
---------------
Jacob Skaria


"jkiser" wrote:

I have a list of machines (MACHINE LIST) and another list of dates (DATE
LIST). The DATE list has the dates that each machine was used . The DATE
list is much longer than the MACHINE list because each machine has been used
multiple times on different dates.

I want to make a report that shows the last date each machine was used. I
could accomplish this by sorting the DATE list and then manually comparing
the two lists. But, I'd have to do that every time they were updated. I
want to automate the process.

Is there a formula that would give me the max date from the DATE list for
each entry on the MACHINE list.....something like a "MAXIF"

Thanks

Max Value of conditional list
 

Both your and Jacobs responses worked perfectly! I've used array formulas in
the past but I never really took the time to learn how to use them well.

Thanks so much.

"JBeaucaire" wrote:

With no knowledge of your sheet's actual layout, you'll have to interpret this.

A1-A6 = Machine1,Machine2,Machine3, etc....
B1-B6 = the last date the machine was used (we'll come back to this)

C1-C100 = machine names
D1-D100 = dates the machines in "c" were used

In B1, enter this array formula:

=MAX(IF($C$1:$C$100=A1,$D$1:$D$100,""))

...confirmed with CTRL-SHIFT-ENTER. Curly braces { } will appear around your
formula and the last date for Machine1 will appear. Now copy that cell down
through B6.


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"jkiser" wrote:

I have a list of machines (MACHINE LIST) and another list of dates (DATE
LIST). The DATE list has the dates that each machine was used . The DATE
list is much longer than the MACHINE list because each machine has been used
multiple times on different dates.

I want to make a report that shows the last date each machine was used. I
could accomplish this by sorting the DATE list and then manually comparing
the two lists. But, I'd have to do that every time they were updated. I
want to automate the process.

Is there a formula that would give me the max date from the DATE list for
each entry on the MACHINE list.....something like a "MAXIF"

Thanks

Max Value of conditional list
 

I got confused and but your formula worked great when I changed the "D1"
references need to be "C1" and followed your directions. Now I gotta go
figure out why it works.

Thanks so much for your help!!

"Jacob Skaria" wrote:

Forgot to mention to format D1 as Date.

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Suppose I have the machin list from Col A starting from row 1 and the dates
in ColB.

Cell C1 = Machine 1
In D1 enter the below formula. If the machine is not found it will return
"NOT USED"

=IF(COUNTIF(A$1:A1$00,D1)0,SUMPRODUCT(MAX(($A$1:$ A$100=D1)*($B$1:$B$100))),"NOT USED")


If this post helps click Yes
---------------
Jacob Skaria


"jkiser" wrote:

I have a list of machines (MACHINE LIST) and another list of dates (DATE
LIST). The DATE list has the dates that each machine was used . The DATE
list is much longer than the MACHINE list because each machine has been used
multiple times on different dates.

I want to make a report that shows the last date each machine was used. I
could accomplish this by sorting the DATE list and then manually comparing
the two lists. But, I'd have to do that every time they were updated. I
want to automate the process.

Is there a formula that would give me the max date from the DATE list for
each entry on the MACHINE list.....something like a "MAXIF"

Thanks