Selecting Last Record

[NWO]

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

 

[Sheeloo]

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

 

[Sheeloo]

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.