Max Value of conditional list

[jkiser]

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

 

[JBeaucaire]

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.

 

[Jacob Skaria]

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

 

[T. Valko]

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.

 

[Jacob Skaria]

Forgot to mention to format D1 as Date.

If this post helps click Yes

 

[jkiser]

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.

 

[jkiser]

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!!