• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find latest date record - URGENT

N

newbeee

New Member
I have a file containing date and time in one column, code, description, status and user in other columns.

I will have code and description same or different with different dates and status changed on each date.

I need a macro which can find out the status and user of the record with latest date.

For ex:


Date Code Description Status user

10/03/2008 8:15 A123 Main Data Read M

11/03/2008 6:15 A123 Main Data Write M

12/03/2008 3:15 A123 Main Data Read M

13/03/2008 7:15 A123 Main Data Write M

12/03/2008 3:15 A123 Test Data Read T

13/03/2008 7:15 A123 Test Data Write T

11/03/2008 8:15 B456 Prod Data Read P

12/03/2008 4:15 B456 Prod Data Write P

14/03/2008 2:15 B456 Test Data Write T

17/03/2008 1:15 B456 Test Data Read T


Output:

13/03/2008 7:15 A123 Main Data Write M

13/03/2008 7:15 A123 Test Data Write T

12/03/2008 4:15 B456 Prod Data Write P

17/03/2008 1:15 B456 Test Data Read T
 
Hi Kanti,


Hope it should be clear now.


Time| Code| Description| Status| User

10/03/2008 8:15 |A123| Main Data| Read| M

11/03/2008 6:15 |A123 | Main Data | Write| M

12/03/2008 3:15 |A123 | Main Data | Read | M

13/03/2008 7:15 |A123 | Main Data | Write | M

12/03/2008 3:15 |A123 | Test Data | Read | T

13/03/2008 7:15 |A123 | Test Data | Write | T

11/03/2008 8:15 |B456 | Prod Data | Read | P

12/03/2008 4:15 |B456 | Prod Data | Write | P

14/03/2008 2:15 |B456 | Test Data | Write | T

17/03/2008 1:15 |B456 | Test Data | Read | T


Output:

13/03/2008 7:15| A123 |Main Data| Write|M

13/03/2008 7:15| A123 |Test Data| Write|T

12/03/2008 4:15| B456 |Prod Data| Write|P

17/03/2008 1:15| B456 |Test Data| Read |T
 
Hi Newbee,


I am not sure of your output, for B456 you have the 17/03/2008 after 12/03/08.


Are you using time only as the key or date and time?


The best solution is to sort your data in the order that you want to see it. That is first by Code, Then by Date and Then by Time


regards


kanti
 
Hi Kanti,


Here the key is combination of Code and Description.

And I am taking date and time into consideration for finding latest record.

A record can change its status any number times in a day.

So I need to find what is last status on that record ie., finding latest data and time of the same record.

If you see here,

11/03/2008 8:15 |B456 | Prod Data | Read | P

12/03/2008 4:15 |B456 | Prod Data | Write | P

The same record is updated on 12th with its status 'Write'.


and here,

14/03/2008 2:15 |B456 | Test Data | Write | T

17/03/2008 1:15 |B456 | Test Data | Read | T

The same record is updated on 17th with its status 'Read'.


That is the reason in the output you have noticed like that.
 
Hi Nwebee,


You can sort the Data first on Code, then Description, then Date and then time, that would give you the desired results.


Kanti
 
Hi Kanti,


I have around 7,200 records and I am not sure how to sort. Can you please give the code so that I will test. I would like to have a marco and later I would like to add some functionality as well.


Thanks.
 
Hi Newbee,


There is no code.


What version of Excel are you suing and do you know how to sort the data?


kanti
 
Hi NewBee


refer to this article


http://spreadsheets.about.com/od/othertips/qt/080704_2003sort.htm


kanti
 
You must log in or register to reply here.
Back
Top