Excel Get Last Row Based On Column Value

[Nu2Java]

Hi.. I have a text file here that will import to a sheet. Could someone tell me how I can get the most recent row and/or the last row of data based on the Badge #. I expect that the last row from each badge # will always be PASSED, but I want to strip out all other rows. Thanks for any direction someone can provide.

Badge# 0005520, Test FAILED ON: 11-01-2020 08:21:44 PM
Badge# 0005520, Test FAILED ON: 11-01-2020 08:21:44 PM
Badge# 0005520, Test PASSED ON: 11-01-2020 08:21:45 PM
Badge# 0005520, Test PASSED ON: 11-01-2020 08:21:45 PM
Badge# 0005345, Test PASSED ON: 11-01-2020 08:21:47 PM
Badge# 0005345, Test PASSED ON: 11-01-2020 08:21:48 PM
Badge# 0005345, Test PASSED ON: 11-01-2020 08:21:48 PM
Badge# 0005269, Test FAILED ON: 11-01-2020 08:21:58 PM
Badge# 0005269, Test FAILED ON: 11-01-2020 08:21:58 PM
Badge# 0005269, Test FAILED ON: 11-01-2020 08:21:59 PM
Badge# 0005269, Test FAILED ON: 11-01-2020 08:21:59 PM
Badge# 0005269, Test FAILED ON: 11-01-2020 08:22:00 PM
Badge# 0005269, Test FAILED ON: 11-01-2020 08:22:00 PM
Badge# 0005269, Test PASSED ON: 11-01-2020 08:22:01 PM
Badge# 0005269, Test PASSED ON: 11-01-2020 08:22:01 PM

 

[vletm]

Nu2Java
Based Your sent some text-lines ...
Have You with Your imported Excel-file...
> ... filtered those Your expected PASSED rows and after that sorted with date/time?
or
> ... tested to solve Your challenge with a Pivot-table?

 

[Marc L]

Hi,​

​

Nu2Java as you forgot to attach a text file and the expected result workbook accordingly, must see forum rules …​

​

 

[Nu2Java]

Hi All... Sorry for my ignorance not posting a workbook. Here is the attached excel file with what I am trying to get at. Just a note, I would prefer if possible not to filter. I would like to delete all of the other data. I have marked the file the best I can and will hopefully show what I need. Thanks

 

[vletm]

Nu2Java
Based Your given sample data ( have to delete Your hopefully show )
... press that button

 

[Nu2Java]

Thank you vletm, this does exactly what I want. If you don't mind, could you explain on the lines what this is doing? Thank you for your time and help, greatly appreciated

 

[vletm]

Nu2Java
Your: what this is doing?
... hmm?
... something like those Your #1 & #4 replies, I've tried to do.
... and You could run it line-by-line to see - what is going on? ( if You 'hide' those Application.ScreenUpdating -lines )
Of course, shorter userfriendly version would to do this from Your text-file ... but then You should remember to have a backup from Your source-file.

 

[Marc L]

Sorry for my ignorance not posting a workbook.

You forgot to attach the text file as I asked for in post #3 …​

​

 

[Nu2Java]

Marc L - I wanted to completely take out the text file as the data is going to change and most likely become a .csv

 

[vletm]

Nu2Java
Do You want to do Yourself that 'text-file' to Excel-file or not?
Do You need that file after 'deletion' as 'text-file' or Excel-file?
So far, You have offered Excel-file
... .csv-file can read as 'text-file'.

 

[Nu2Java]

Nu2Java
Do You want to do Yourself that 'text-file' to Excel-file or not?
Do You need that file after 'deletion' as 'text-file' or Excel-file?
So far, You have offered Excel-file
... .csv-file can read as 'text-file'.

vletm Yes, I will actually take care of that part as I am having no issue there "yet". Your macro works great for what I need so far.