Isolating single row based on date comparison

[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.

 

[Guest]

Not sure i got ur right but try:

Put this in F1 and copy down:
=IF(COUNTIF(A1:$A$100,A1)=1,A1,"")

select column F1:F12
rightclick in selection and select COPY
rightclick in selection and select PASTE SPECIEL
select VALUES
ok
select F1:F12
Data>Sort - to move values in column F up

Put this in G1 and copy down
=VLOOKUP(F1,A112,4,FALSE)


"(e-mail address removed)" skrev:

 

[aprilheeler]

Thanks, close, but what I need to determine is which value in column C
(N, 2 or 3) is appropriate as of the date in column D. I tried
changing the 4 in

=VLOOKUP(F1,A112,4,FALSE)

to 3 to return the column C values instead of the column D dates, but
it looks like that gives me the column C value from the first row for
each unique column A value.

I'm thinking that there must be a way to put a formula in column E that
would return a TRUE or FALSE (or any other indicator) like so:

I haven't been able to come up with a formula that says

-Compare the date in column D with the date in column B
-If the date in column D is before the date in column B, see if the
value in column A for this row is the same as for the value in column A
for the next row
-If the column A value for the next row is different, then this is the
row I want because the date in D is before the date in B and this is
the last instance of this value of A, so put some text in column E
-If the column A value for the next row is the same, go to the next row
and see if the date in column D is before the date in column B
-If it is, continue checking rows until *either* the A value on the
next row is different and the date in column D is still before column B
(in which case put some text in column E) *or* the A value on the next
row is the same but the date in column D on the next row is after the
date in column B on the next row (and put some text in column E)

Erk. If it helps to know what the information represents, the
spreadsheet represents a claim number (A), the date as of which a
particular benefit plan went into effect (B), the type of coverage
assigned (C), and the date the claim was incurred (D). I need to
determine the type of coverage that was in place at the time the claim
was incurred. The type of coverage can change as time goes on so I
need to connect the incurred date to the type of coverage in effect at
the time.

 

[aprilheeler]

Ack. Now I'm confusing myself even more. Maybe working backward
from the desired result will help:

Row 3 is selected because for column A value 12345, 05/11/05 in column
D is after 01/01/05 in column B (on the same row) and before 05/01/06
in column B (on the next row).

Row 6 is selected because for column A value 56789, 10/12/06 in column
D is after 02/01/06 in column B (on the same row) and there are no
later column B dates for 56789 (the next row has a different column A
value).
Row 7 is selected because for column A value 68765, 07/31/05 in column
D is after 01/01/99 in column B (on the same row) and before 10/01/06
in column B (on the next row).
Row 9 is selected because for column A value 80101, there is only a
single row and, by default, this has to be the correct record.
Row 10 is selected because for column A value 95555, 04/13/05 is after
01/01/99 in column B (on the same row) and before 05/01/06 in column B
(on the next row).
Sorry so wordy.