Limiting data to the oldest date

[cdnqte]

I have a table that stores equipment readings by date. We need to filter down to just the most current reading per unit.

The table appears as follows:

A(Unit) B(Reading1) C(reading2) Date
20000 12 12 8/23/13
20000 12 12 1/22/14
20000 0 0 06/25/14
20000 11 11 01/05/18
20001 8 8 05/31/16
20001 4 4 12/31/17


So I want to identify a column that identifies which row per UNIT has the most current date.

I assume this would use the max formula but I can't get it to be based on the unit number column as well.

HELP!

 

[sgroath]

Assuming your headers and data shown resides in cells A1:D7, add a new column header "Most Current" in E1 and try this formula in E2:

=MAX((D$2:D$7)*(A$2:A$7=A2))=D2

Once you have the formula in, hit Ctrl+Shift+Enter (instead of just Enter) to make this an array function. It will show up in the formula bar as:

{=MAX((D$2:D$7)*(A$2:A$7=A2))=D2}

The braces around the formula define it as an array formula and should NOT be typed in.

Copy that formula down to E3:E7. You will now have a column with results of TRUE or FALSE showing if this is the most recent reading for the unit number. Reply if you don't understand how this array function works.