Creating a date Sequence with the removal of the odds

Hello

I'm doing some sorting but I can't figure out how to do so...

I have about four columns

The First column

Is written like that

1/1/2010 00:00:00
1/1/2010 00:10:00
1/1/2010 00:20:00
1/1/2010 00:30:00
1/1/2010 00:40:00
1/1/2010 00:50:00
1/1/2010 01:00:00
1/1/2010 01:10:00
1/1/2010 01:20:00
1/1/2010 01:30:00
1/1/2010 01:40:00
1/1/2010 01:50:00
1/1/2010 02:00:00
1/1/2010 02:10:00
1/1/2010 02:20:00
1/1/2010 02:30:00
1/1/2010 02:40:00
1/1/2010 02:50:00
...
...

2/1/2010 00:00:00
2/1/2010 00:10:00
2/1/2010 00:20:00
2/1/2010 00:30:00
2/1/2010 00:40:00
2/1/2010 00:50:00
2/1/2010 01:00:00
2/1/2010 01:10:00
2/1/2010 01:20:00
2/1/2010 01:30:00
2/1/2010 01:40:00
2/1/2010 01:50:00
2/1/2010 02:00:00
2/1/2010 02:10:00
2/1/2010 02:20:00
2/1/2010 02:30:00
2/1/2010 02:40:00
2/1/2010 02:50:00

and so on..

Sometimes The machine is stop working and also stop recording to excel which will be like that

1/1/2010 00:00:00
1/1/2010 00:10:00
1/1/2010 00:40:00
1/1/2010 00:50:00
1/1/2010 01:00:00
1/1/2010 01:10:00
1/1/2010 01:20:00
1/1/2010 01:30:00
1/1/2010 01:40:00
1/1/2010 01:50:00

all I need to put a code that deletes all rows that it's group is not complete like
1/1/2010 00:00:00
1/1/2010 00:10:00
1/1/2010 00:40:00
1/1/2010 00:50:00
1/1/2010 01:00:00
1/1/2010 01:10:00
1/1/2010 01:20:00
1/1/2010 01:30:00
1/1/2010 01:40:00
1/1/2010 01:50:00

which will leave only these rows
1/1/2010 01:00:00
1/1/2010 01:10:00
1/1/2010 01:20:00
1/1/2010 01:30:00
1/1/2010 01:40:00
1/1/2010 01:50:00

(adjust the following formula to your range of data)

So maybe out in your empty column (G1?) put:

=IF(SUMPRODUCT((INT($A$1:$A$10)=INT(A1))*(LEFT(ROUND(($A$1:$A$10-INT($A$1:$A$10))*24,6))=LEFT(ROUND((A1-INT(A1))*24,6))))<>6,1/0,"") and copy down

1. Then select G
2. Hit F5
3. Choose Special...
4. Under Formulas uncheck Logical, Numbers, and Text (leaving only errors)
5. Hit Okay

1. Go to 'Cells' in Toolbar
2. Expand Menu beneath Delete
3. Choose Delete Sheet Rows

Could also be adapted for VBA.

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I'm sorry, but the corporate firewall prevents downloading files from p2p sites.

Here's a workbook with your example and the steps.

I see. Instead, let's just blend my solution with hohlick.

They were much closer to the answer, minus the same day criteria.

C1 and copied down:
=IF(SUMPRODUCT((INT($A$1:$A$8199)=INT(A1))*(HOUR($A$1:$A$8199)=HOUR(A1)))<>6,1/0,"")

I don't have Excel at home, but will gladly examine it on Monday.