Complex Date and Address Problem

[Jay Virdee]

Hi All,

I have a situation that I cannot solve and need to produce a formula for. The situation is the following. I have a table with a unique person ID and then their past and current addresses. These addresses have a From and To Date. I then have a form which was completed at a certain date and want to find at which address the person was living when that form was completed. Now this would be easy if we did not have data quality issues. Often the To Date is missing so we need to then compare the two from dates to find out at which address the form was completed.

E.g

ID	Address	From	To
555	1	03/04/2017
555	2	10/11/2016
555	3	03/05/2016	10/11/2016
Form Done	15/02/2017

In this example the address the form was completed at was address 2. 

Anyone have any ideas? 

[#2]

How about adding an extra 'Best guess' column that you can check against if the To column is empty.

This would work I think: - entered as an array (ctrl+shift+enter)

=IF(ISBLANK(D2),IFERROR(INDEX($C$2:$C$7,MATCH((B2 - 1) & A2,$B$2:$B$7 & $A$2:$A$7,0)),TODAY()),"")

Ends up looking like this:

ID	Address	From	To	Best Guest
555	1	03/04/2017		14/8/18
555	2	10/11/2016		3/4/17
555	3	03/05/2016	10/11/2016
444	1	5/8/16		14/6/18
444	2	3/7/16	4/8/16
444	3	4/6/16		3/7/16
Form Done	15/02/2017

(14/08/16 in this case would be todays date)

[#3 · Accepted solution]

Dear Jay Virdee,

Kindly refer to the formulas below for

1. Finding the Address on the particular Date. (Cell H2, coloured Green)

=INDEX($B$1:$B$10000,MATCH(F2&" "&MAX(IF($C$1:$C$10000<=$G2,$C$1:$C$10000,0)*IF($A$1:$A$10000=$F2,1,0)),$A$1:$A$10000&" "&$C$1:$C$10000,0),1)

2. Finding the 'TO' Date. (Cell H2, coloured Yellow)

=MIN(IFERROR(IF($C$2:$C$100>C2,$C$2:$C$100,"-")*IF($A$2:$A$100=A2,1,"-")-1,TODAY()))

Notes:

1. The formulas work correctly even when the dates are not sorted.

2. Both the formulas are Array Formulas. They need to be entered using Control + Shift + Enter, instead of Enter.

3. Change the cell references as required. Refer to the attached photograph.

4. To find the address on a particular Date, I have not used the 'To' Date. All dates till the next change of address for the particular ID have been considered to be at the previous address.

5. For the last entry of date for each ID, Today() has been used.

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India

[#4]

Thanks this works really well.