A
aprilheeler
I have a spreadsheet with columns A, B, C and D as follows:
1 :12345 01/01/02 3 05/11/05
2 :12345 01/01/03 3 05/11/05
3 :12345 01/01/05 N 05/11/05
4 :12345 05/01/06 T 05/11/05
5 :56789 04/01/04 2 10/12/06
6 :56789 02/01/06 N 10/12/06
7 :68765 01/01/99 3 07/31/05
8 :68765 10/01/06 2 07/31/05
9 :80101 01/01/05 N 08/24/06
10:95555 01/01/99 N 04/13/05
11:95555 05/01/06 3 04/13/05
12:95555 10/01/06 T 04/13/05
Data are sorted by column A, then by column B.
I need a method to filter the rows such that I have a single row for
each value in column A, and the date in column D for that row is after
the date in column B. This will tell me which value for column C was
in place as of the date in column D.
In the above example, it would return rows 3 (for value 12345, 03/11/05
is after 01/01/05 but before 05/01/06), 6 (for value 56789, 10/12/06 is
after 02/01/06 and there is no other row for 56789 with a date after
10/12/06), 7 (for value 68765, 07/31/05 is after 01/01/99 but before
10/01/06), 9 (for value 80101, 08/24/06 is after 01/01/05 and there is
no other row for 10101 with a date after 08/24/06) and 10 (for 95555,
04/13/05 is after 01/01/99 but before 05/01/06 and 10/01/06).
What formula could I put in column E that would allow me to filter the
rows for the results I need?
Many thanks for your help.
1 :12345 01/01/02 3 05/11/05
2 :12345 01/01/03 3 05/11/05
3 :12345 01/01/05 N 05/11/05
4 :12345 05/01/06 T 05/11/05
5 :56789 04/01/04 2 10/12/06
6 :56789 02/01/06 N 10/12/06
7 :68765 01/01/99 3 07/31/05
8 :68765 10/01/06 2 07/31/05
9 :80101 01/01/05 N 08/24/06
10:95555 01/01/99 N 04/13/05
11:95555 05/01/06 3 04/13/05
12:95555 10/01/06 T 04/13/05
Data are sorted by column A, then by column B.
I need a method to filter the rows such that I have a single row for
each value in column A, and the date in column D for that row is after
the date in column B. This will tell me which value for column C was
in place as of the date in column D.
In the above example, it would return rows 3 (for value 12345, 03/11/05
is after 01/01/05 but before 05/01/06), 6 (for value 56789, 10/12/06 is
after 02/01/06 and there is no other row for 56789 with a date after
10/12/06), 7 (for value 68765, 07/31/05 is after 01/01/99 but before
10/01/06), 9 (for value 80101, 08/24/06 is after 01/01/05 and there is
no other row for 10101 with a date after 08/24/06) and 10 (for 95555,
04/13/05 is after 01/01/99 but before 05/01/06 and 10/01/06).
What formula could I put in column E that would allow me to filter the
rows for the results I need?
Many thanks for your help.