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 :)

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 :)

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 :)

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 :)

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 :)

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 :)