Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Last Record
Hello
I have a list of 40,000 records. The records are grouped by a receipt number, a receipt date, and time stamp. So, for example, there can be 10 records with the same receipt number, 4 different dates, with each record having a time stamp. Now the list is sorted in the following order - Receipt Number, Date, Time Stamp. What I am attempting to accomplish is to identify the last record in each record set based on the most recent date,and ifmore than one date then the latest time stamp. How would I accomplish this using Excel? Thank you. Mark :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Last Record
As I understand that your data is sorted by receipt number by date by time
stamp (all in ascending order) meaning that first record is oldest and last record is the latest for a given receipt number and you want to identify the last record for a receipt number... Assuming receipt numbers are in Col A upto row 40000 enter this in any column and first row =SUMPRODUCT(--(A1:$A$40000=A1)) and copy down till row 40000 Cells with value 1 will indicate the last row for the corresponding receipt number. Basically you will get the no of rows for a given receipt number in descending order agains that receipt number... "NWO" wrote: Hello I have a list of 40,000 records. The records are grouped by a receipt number, a receipt date, and time stamp. So, for example, there can be 10 records with the same receipt number, 4 different dates, with each record having a time stamp. Now the list is sorted in the following order - Receipt Number, Date, Time Stamp. What I am attempting to accomplish is to identify the last record in each record set based on the most recent date,and ifmore than one date then the latest time stamp. How would I accomplish this using Excel? Thank you. Mark :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Last Record
Thnak you Sheeloo.
The solution worked perfect, well almost until I discovered that we have some records within the total 40000 record set that have the same receipt number, date, and time stamp, but have different action codes (I didn't tmention the actions codes in my original e-mail - sorry). So, how would you adjust the function to show a 1 for each of these duplicate records, if possible? Thnak you again for your very logical reasoning and understanding my question the first go around. NWO "Sheeloo" wrote: As I understand that your data is sorted by receipt number by date by time stamp (all in ascending order) meaning that first record is oldest and last record is the latest for a given receipt number and you want to identify the last record for a receipt number... Assuming receipt numbers are in Col A upto row 40000 enter this in any column and first row =SUMPRODUCT(--(A1:$A$40000=A1)) and copy down till row 40000 Cells with value 1 will indicate the last row for the corresponding receipt number. Basically you will get the no of rows for a given receipt number in descending order agains that receipt number... "NWO" wrote: Hello I have a list of 40,000 records. The records are grouped by a receipt number, a receipt date, and time stamp. So, for example, there can be 10 records with the same receipt number, 4 different dates, with each record having a time stamp. Now the list is sorted in the following order - Receipt Number, Date, Time Stamp. What I am attempting to accomplish is to identify the last record in each record set based on the most recent date,and ifmore than one date then the latest time stamp. How would I accomplish this using Excel? Thank you. Mark :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Last Record
You are welcome..
Assuming action codes are in Col D try =SUMPRODUCT(--(A1:$A$40000=A1),--(D1:$D$40000=D1)) "NWO" wrote: Thnak you Sheeloo. The solution worked perfect, well almost until I discovered that we have some records within the total 40000 record set that have the same receipt number, date, and time stamp, but have different action codes (I didn't tmention the actions codes in my original e-mail - sorry). So, how would you adjust the function to show a 1 for each of these duplicate records, if possible? Thnak you again for your very logical reasoning and understanding my question the first go around. NWO "Sheeloo" wrote: As I understand that your data is sorted by receipt number by date by time stamp (all in ascending order) meaning that first record is oldest and last record is the latest for a given receipt number and you want to identify the last record for a receipt number... Assuming receipt numbers are in Col A upto row 40000 enter this in any column and first row =SUMPRODUCT(--(A1:$A$40000=A1)) and copy down till row 40000 Cells with value 1 will indicate the last row for the corresponding receipt number. Basically you will get the no of rows for a given receipt number in descending order agains that receipt number... "NWO" wrote: Hello I have a list of 40,000 records. The records are grouped by a receipt number, a receipt date, and time stamp. So, for example, there can be 10 records with the same receipt number, 4 different dates, with each record having a time stamp. Now the list is sorted in the following order - Receipt Number, Date, Time Stamp. What I am attempting to accomplish is to identify the last record in each record set based on the most recent date,and ifmore than one date then the latest time stamp. How would I accomplish this using Excel? Thank you. Mark :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Last Record
Thnakyou again.
Formula generates 1s and 2s only and dosn't appear to distinghish between the two. Ideally, if there are, say, 10 records with the same receipt number, and two of these records have the same date (say the most recent date), same time, but 2 different action codes, then I would llike to see, say, 1,1,9,8,7,6,5,4,3,2. Does this make sense? Mark :) "Sheeloo" wrote: You are welcome.. Assuming action codes are in Col D try =SUMPRODUCT(--(A1:$A$40000=A1),--(D1:$D$40000=D1)) "NWO" wrote: Thnak you Sheeloo. The solution worked perfect, well almost until I discovered that we have some records within the total 40000 record set that have the same receipt number, date, and time stamp, but have different action codes (I didn't tmention the actions codes in my original e-mail - sorry). So, how would you adjust the function to show a 1 for each of these duplicate records, if possible? Thnak you again for your very logical reasoning and understanding my question the first go around. NWO "Sheeloo" wrote: As I understand that your data is sorted by receipt number by date by time stamp (all in ascending order) meaning that first record is oldest and last record is the latest for a given receipt number and you want to identify the last record for a receipt number... Assuming receipt numbers are in Col A upto row 40000 enter this in any column and first row =SUMPRODUCT(--(A1:$A$40000=A1)) and copy down till row 40000 Cells with value 1 will indicate the last row for the corresponding receipt number. Basically you will get the no of rows for a given receipt number in descending order agains that receipt number... "NWO" wrote: Hello I have a list of 40,000 records. The records are grouped by a receipt number, a receipt date, and time stamp. So, for example, there can be 10 records with the same receipt number, 4 different dates, with each record having a time stamp. Now the list is sorted in the following order - Receipt Number, Date, Time Stamp. What I am attempting to accomplish is to identify the last record in each record set based on the most recent date,and ifmore than one date then the latest time stamp. How would I accomplish this using Excel? Thank you. Mark :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Last Record
I am not clear about the requirement...
The formula I had given will give the no of times same value of A & D together occur in the data against the first occurrence, one less against the next and 1 against the last. If there are only two records with the same combination then first will show 2 and second 1... If you have date in B and time in C then the following will give the same result for the combination of A,B,C & D =SUMPRODUCT(--(A1:$A$40000=A1),--(B1:$B$40000=B1),--(C1:$C$40000=C1),--(D1:$D$40000=D1)) Following will give you the count of values in A against each value =SUMPRODUCT(--($A$1:$A$40000=A1) You may combine these (and IF statements) to get what you want... Let me know if you know help. You can mail the file to me if you want. "NWO" wrote: Thnakyou again. Formula generates 1s and 2s only and dosn't appear to distinghish between the two. Ideally, if there are, say, 10 records with the same receipt number, and two of these records have the same date (say the most recent date), same time, but 2 different action codes, then I would llike to see, say, 1,1,9,8,7,6,5,4,3,2. Does this make sense? Mark :) Mark :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Choose next record | Excel Discussion (Misc queries) | |||
Record New Macro | Setting up and Configuration of Excel | |||
HOW DO YOU GET A RECORD OF EMAILS SENT ? | Excel Discussion (Misc queries) | |||
Always show new record | Excel Discussion (Misc queries) |