Date / time spacing

[Bebbspoke]

Hi - I have numerous historic data files - all are date & time stamped in a single column. All data is at five minute intervals over a ten week period. The data is in contiguous rows - there are no gaps; but sporadically the data is not contiguous - individual or strings of time stamps are absent, and different files may have different gaps!
Please - is there a method whereby Excel can sort the data such that blank rows are inserted for the missing time stamps? - such would enable me to align all the files for rapid comparison. Thank you.

 

[Bebbspoke]

Thank you Comfy - this solves my problem

 

[Comfy]

Thank you Comfy - this solves my problem

You're Welcome.

 

[Bebbspoke]

Hi Comfy; -



Thank you for your assistance.



I neglected to tell you that the single column data I sent you was directly from the original source data file... this I subsequently import into a processing file commencing Row #4 as the first THREE rows are as headers to the processing.

Your solution works fine on original data but I cannot apply your macro to files which I've already processed. Ho hum...

 

[Comfy]

This:

For i = 2 To LR - 1

Is the line that you need to change.

Change the two to be the row number where your data starts.

 

[Bebbspoke]

Thank you for the instruction to "fix" - I'm currently running the fix on a previously processed file... it's taking an age to process (been over 30mins so far... ~15k rows and 70 cols) - I'll let you know when it's done... Cheers, Bebbspoke

 

[Comfy]

You can stop it by pressing Ctrl + Break

With some more development time it would better if you utilised a Data Connection and/or Access.

Although I'm no expert I'm willing to help if I can.

 

[Bebbspoke]

You can stop it by pressing Ctrl + Break

With some more development time it would better if you utilised a Data Connection and/or Access.

Although I'm no expert I'm willing to help if I can.

Well - I'll let it run to finish - to see how long it'll take! - (still running over an hour now - and this is certainly NOT a slow pc - AMD 8320 8 core with 16G of ram).

Your offer of further assistance is much appreciated and accepted - there is a far more complex aspect of the processing which currently has to be done manually - this takes about 3 hours per file and with a minimum of 16 files that is not amusing... it may take me a while to explain the issue and to procure "clutter free" examples for your perusal. Cheers, Bebbspoke

 

[Bebbspoke]

It took about 1h 20mins to process - don't really care too much 'cos it gets the job done (assumed) error free... and it's a lot less eyestrain from doing it manually! Chers.

 

[Comfy]

Well - I'll let it run to finish - to see how long it'll take! - (still running over an hour now - and this is certainly NOT a slow pc - AMD 8320 8 core with 16G of ram).

Your offer of further assistance is much appreciated and accepted - there is a far more complex aspect of the processing which currently has to be done manually - this takes about 3 hours per file and with a minimum of 16 files that is not amusing... it may take me a while to explain the issue and to procure "clutter free" examples for your perusal. Cheers, Bebbspoke

You have my email so just respond there.

My GPU is on its last legs so I now have some free time in the evenings