Removing records

[Phil]

I want to remove all records with the same identifier
between the earliest date and the latest date so I'm left
with 2 records only per identifier.

Currently file looks like this;
ID DATE
41491 10-Feb-03
41491 10-Feb-03
41491 7-Apr-03
41491 7-Apr-03
41491 6-Jun-03
41491 6-Jun-03
41492 10-Feb-03
41492 4-Apr-03
41494 17-Feb-03
41494 28-Apr-03
41497 7-Mar-03
41497 7-Mar-03
41497 1-Apr-03
41497 1-Apr-03
41499 17-Mar-03
41499 17-Mar-03

I want it to end up looking like this;
41491 10-Feb-03
41491 6-Jun-03
41492 10-Feb-03
41492 4-Apr-03
41494 17-Feb-03
41494 28-Apr-03
41497 7-Mar-03
41497 1-Apr-03
41499 17-Mar-03
41499 17-Mar-03

Once this has been achieved I will then calculate the
elapsed time between start and finish.

Any thoughts?

Phil

 

[Biff]

Hi Phil!

Not exactly what you want but very close. Would this setup
be acceptable?

41491 6-Jun-03 10-Feb-03
41492 4-Apr-03 10-Feb-03
41494 28-Apr-03 17-Feb-03
41497 1-Apr-03 7-Mar-03
41499 17-Mar-03 17-Mar-03

Then you could do your elapsed time calculation in the
next column over.

Let me know.

Biff

 

[Phil]

Thanks Biff,

Your format is fine, better than mine in fact. I
understand doing the calculation but I still need to work
out how to remove all the other records for each
individual ID that fall between the earliest and latest
date.

Any thoughts on that?

 

[Biff]

Hi Phil!

No problem but it will take a few steps.

The first thing to do is to create the list of ID's.
You're going to create a new list from the old list. Once
you do that then you can decide if you want to keep the
old list or just delete it.

The new list: (very simple)

Have in mind where you want the new list to appear. For
the sake of this example I'll use D1 and assume your
current list is in cols A and B - A1:B17.

- Select the range of ID's including the header.
- Goto Data>Filter>Advanced Filter
- Check: copy to another location
- The list range should already be entered but if it isn't
enter the range of ID's including the header.
- In copy to: enter where you want the new list to appear,
D1
- Check: unique records only
- OK out

Now you have a new list of ID's in D1 that should look
like this:

ID
41491
41492
41494
41497
41499

Now, in cells E2 and F2 you need to enter one of two
formulas depending on how you want the format. Use the
first formula in E2 if you want the earliest date to
appear first and use the second formula in F2 if you want
the latest date to appear second. Or just switch them
around. Adjust the range references to suit. Col B are the
dates.

Earliest date (start date):

=INDEX($B$2:$B$17,MATCH(MIN(IF
($A$2:$A$17=$D2,$B$2:$B$17)),$B$2:$B$17,0))

Latest date (end date):

=INDEX($B$2:$B$17,MATCH(MAX(IF
($A$2:$A$17=$D2,$B$2:$B$17)),$B$2:$B$17,0))

These are array formulas. They must be entered using the
key combination of CTRL SHIFT ENTER.

After you have the two formulas entered, select both cells
E2 and F2. Then either drag copy down or just double click
the fill handle.

There you go.

Now, if you want to delete the original list you'll have
to change the formulas to constants or you'll get errors.
To do that select the range of formulas and do: Copy>Paste
Special>Values. Then you can safely delete the original
list.

Kind of long winded but it only takes a few minutes to
complete.

Biff

 

[Dave Peterson]

Another way if you like Biff's layout and your dates are sorted in ascending
order within each ID--and the ID's are sorted.

Put this in C2 and drag down
=IF(A2=A1,"",B2)
(Just picks up the first date in the group.

Put this in D2 and drag down:
=IF(A2=A1,"",MAX(IF(A2:A999=A2,B2:B999)))
(change 999 to more than the number of rows you're using)

Now select columns C
edit|copy
Edit|paste special values

Apply data|Filter|autofilter to column C.
filter to show blanks
delete those visible rows

Remove the filter.

 

[Phil]

Dave,

Thanks. The first part of your suggestion works perfectly
picking out the first date and blanking the rest.
Unfortunately I'm having a problem still with last date.
Your formula gives a date which is the latest date for any
ID, not for each individual ID.

Any further thoughts?

Phil

 

[Dave Peterson]

Sorry, I left out one very important thing:

This formula:
=IF(A2=A1,"",MAX(IF(A2:A999=A2,B2:B999)))
is an array formula.

Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap
curly brackets {} around your formula. (don't type them yourself.)

Sorry (again).

 

[Guest]

Dave,

Thanks, works fine.

Phil

-----Original Message-----
Sorry, I left out one very important thing:

This formula:
=IF(A2=A1,"",MAX(IF(A2:A999=A2,B2:B999)))
is an array formula.

Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap
curly brackets {} around your formula. (don't type them yourself.)

Sorry (again).




--

Dave Peterson
(e-mail address removed)
.

 

[Phil]

Biff,

Thanks. Like Dave who also replied re this it was the
array formula which was the key.

Phil