Can't sort time data for values ending in '0'

Hi,

I am new to the forum (first post) so apologies if I break convention or don't post as is expected, (I have read the forum rules).

I have some simple data that I am trying to sort by time (see attached file - note I have cut out most of the data to stay within the attachment limit - it is much larger than this in reality down to more than row 200,000).

Basically there are six columns listing financial price data. Column 1 is the date, column 2 is the time, and columns 3-6 are the financial instrument prices. Each column represents one minute, so it looks like the screen shot below.

The data continues over 200,000 rows.

I only need the prices that occur every ten minutes, E.g. the prices at 00:00, then 00:10, then 00:20, then 00:30 etc etc all through the day. (Almost every 10th column, but not always because some data is not-contiguous). I.e. I need only the columns in which the time cell ends in a zero. How would I go about sorting the table to give me only these cells?

If the data was completely contiguous I could just sort every tenth column, but unfortunately there are many instances where some minutes the data is not present so this doesn't work. And with more than 200,000 data points impossible to do manually!

Any suggestions would be greatly appreciated.

You could use a helper column with the following formula in:
=IF(RIGHT(TEXT(B2,"hh:mm"),1)=TEXT(0,"general"),"x","")
then filter out the Xs?

Hope this helps.

Originally Posted by brokenbiscuits

You could use a helper column with the following formula in:
=IF(RIGHT(TEXT(B2,"hh:mm"),1)=TEXT(0,"general"),"x","")
then filter out the Xs?

Hope this helps.

Wow that totally worked! Thank you so much, that really helped me out. I had no idea how to do that, and to be honest, other than the IF function, I have never seen the RIGHT or TEXT functions before. I will go away and read what they do to see how you did it.

Thanks again brokenbiscuits!