Isolating single row based on date comparison

  • Thread starter Thread starter aprilheeler
  • Start date Start date
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.
 
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,A1:D12,4,FALSE)


"(e-mail address removed)" skrev:
 
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,A1:D12,4,FALSE)
Click to expand...

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.
 
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.
 
Back
Top