?HowTo: Return Latest Date in One Field, Based on Matched Criteria in 2 Other Fields

[Default300]

Hi. Can you help me solve this puzzle? Hope so.
I've begun by showing the setup I have, but if you want to read the task and problem first, they're stated below.
Tables have been stripped down to the essential columns; and cell references and sheet names have been simplified.

SCENARIO / SETUP

Table 1 (DeviceDetails) (I'll call the worksheet DvcDtl for this example)

Table of Devices.

Each row / record refers to a single Device with a unique DeviceID, with various Details of that device listed in columns / fields.

There is exactly 1 row / record for each Device.

State		Existing	Proposed / New
Content		Constant	Formula
DataType		Integer	Date
Format		0000	dd/mm/yyyy
		A	B
Header	1	DeviceID	LatestPrintDate
Data	2	0111	31/12/2001
Data	3	0222	29/10/2001
Data	4	0333	30/11/2001

<tbody>
</tbody>

Table 2 (ServiceRecords) (I'll call the worksheet SrvRcd for this example)

Table of Service Records for devices.

Each row is a single Service Event with a unique ServiceEventID, and concerns a single device identified by its unique DeviceID.

There is exactly 1 row / record for each ServiceEvent.

But there may be 0, 1 or multiple ServiceEvents for each Device.

If it were a relational database, then the relationship between the DeviceDetails table and the ServiceRecords table (for the DeviceID field) would be referred to as "one-to-many".

State		Existing	Existing	Existing	Existing	Proposed / New
Content		Constant	Constant	Constant	Constant	Formula
DataType		Integer	Integer	Text	Date	Date
Format		0000	0000	General	dd/mm/yyyy	dd/mm/yyyy
		A	B	C	D	E
Header	1	ServiceEventID	DeviceID	ServiceDocType	ServiceDocDate	LatestPrintDate
Data	2	0995	0111	Printed	29/10/2001	31/12/2001
Data	3	0996	0333	Written	29/10/2001	30/11/2001
Data	4	0997	0222	Printed	29/10/2001	29/10/2001
Data	5	0998	0111	Written	30/11/2001	31/12/2001
Data	6	0999	0333	Printed	30/11/2001	30/11/2001
Data	7	1000	0111	Printed	31/12/2001	31/12/2001

<tbody>
</tbody>

TASK

I want to know what was the most recent date on which a service record was printed (as opposed to written), for each device.

What formula should I use in LatestPrintDate column, to return the date shown?

NB: The LatestPrintDate column can be in either Table, though I think it makes more sense in the DeviceDetails table, where the calculation would be performed once for each device.

It's possible to introduce one or more new columns in either table if required. In fact I'd prefer to have more columns than a very convoluted nested formula.

I'd prefer not to use an array formula. If I must, then I'd prefer one contained within a single cell rather than stretched across a full column.

I'd like to use SUMPRODUCT if possible, as I find that syntax easy to read and edit.
Perhaps MAX, LARGE and/or SUBTOTAL has a part to play (to return the latest date).

If using LARGE, I'd prefer to use a constant to define parameter "K", rather than to use ROW().

If lookups are required, then I prefer INDEX + MATCH rather than VLOOKUP etc.


ATTEMPTED SOLUTION

If inserting proposed new column into the DeviceDetails Table (NOT ServiceRecords Table), then perhaps something similar to this...

=SUMPRODUCT(('SerRec'!$A$2:$A$7='DvcDtl'!$A2)*('SerRec'!$C$2:$C$7="Printed")*(MAX('SerRec'!$D$2:$D$7)))


PROBLEM

Date returned by above formula is wrong.

It is returning the same maximum date for all devices (not the specific device referenced).

It then multiplies this date by the number of instances that the specific device has had a service record printed.

Not what I want, obviously!


Any ideas?

Thanks in advance.

(BTW the table reformatted the alignment of all cells to centered, despite careful setup)

 

[AlphaFrog]

Try something like this....

=MAX(IF((SerRec!$B$2:$B$7=DvcDtl!$A2)*(SerRec!$C$2:$C$7="Printed"), SerRec!$D$2:$D$7))

This is an array formula confirmed with Ctrl+Shift+Enter

 

[Default300]

@ AlphaFrog

Thanks. :wink:

Regarding the array formula...

It's the type where you "array-enter" the formula in a single cell and then fill or copy paste it down the whole column, right?

It's NOT the type where you fill or copy paste the "non array-entered" formula down the whole column; select all those cells; F2; and array-enter it, creating a single array of all the formulae cells in the column, is it?

I'm trying to avoid the latter ones especially, as they make life hard when trying to extend tables.

I don't know what impact array formulae have on calculation speed versus other solutions (if they exist).

I presume you would have offered a non-array equivalent if there was one that you knew of.

Thanks for your suggestion. I'll give it a try.

Cheers.

 

[Default300]

@ AlphaFrog

I gave that a go, and it seems to work, at least on initial testing.

Thanks for your advice and your time. :wink:

If anyone has any other suggestions I'd still be interested to hear them.

Or if anyone has an opinion on my concern re array formulae and calculation time (or links to articles debating the subject for me to read).

Cheers.