Forum Discussion
Complex Date and Address Problem
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?
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
3 Replies
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
- Thanks this works really well.
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)
