If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Removing records
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 |
#2
|
|||
|
|||
Removing records
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 -----Original Message----- 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 . |
#3
|
|||
|
|||
Removing records
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? -----Original Message----- 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 -----Original Message----- 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 . . |
#4
|
|||
|
|||
Removing records
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 DataFilterAdvanced 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: CopyPaste SpecialValues. Then you can safely delete the original list. Kind of long winded but it only takes a few minutes to complete. Biff -----Original Message----- 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? -----Original Message----- 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 -----Original Message----- 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 . . . |
#5
|
|||
|
|||
Removing records
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 wrote: 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 -- Dave Peterson |
#6
|
|||
|
|||
Removing records
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 -----Original Message----- 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 wrote: 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 -- Dave Peterson . |
#7
|
|||
|
|||
Removing records
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). Phil wrote: 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 -----Original Message----- 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 wrote: 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 -- Dave Peterson . -- Dave Peterson |
#8
|
|||
|
|||
Removing records
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). Phil wrote: 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 -----Original Message----- 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 wrote: 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 -- Dave Peterson . -- Dave Peterson . |
#9
|
|||
|
|||
Removing records
Biff,
Thanks. Like Dave who also replied re this it was the array formula which was the key. 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). Phil wrote: 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 -----Original Message----- 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 wrote: 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 -- Dave Peterson . -- Dave Peterson . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
append Query duplicating records | Alex | Running & Setting Up Queries | 1 | July 8th, 2004 01:31 PM |
Query combining multiple records from one table can't add records | Clint Marshall | Running & Setting Up Queries | 4 | July 8th, 2004 01:25 PM |
Copying new records | John | General Discussion | 0 | June 25th, 2004 04:02 PM |
How to Manipulate the group and sorting in a report? | Jorge Novoa | Using Forms | 2 | June 9th, 2004 01:12 AM |
removing duplicate records | Jennifer | Worksheet Functions | 2 | January 5th, 2004 02:28 PM |