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
|