JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Date, Time and Data auto sort fill etc
Hi Folks; - PLEASE; - Raw data is as follows; - Row#1 is headings - all subsequent are data:
Col#A given as Date & Time in format "dd\/mm\/yyyy hh:mm:ss (newest to oldest)" in 1 (one) minute stamps but has some stamps missing.
Col#B thru I is numeric data.
Fill Requirements; -
1/ Col#A reformat to "ddd.dd/mm/yy hh:mm (oldest to newest)" in 1 (one) minute intervals.
2/ Fill: - Col#A missing 1 (one) minute stamps and Col#B thru I with last known data.
3/ Auto filled rows to appear in coloured Italics for identification.
Please will some kind person write me a macro to do this as I have several large data files to process and will repeated get the problem with new data. Thank you.
Cheers, Bebbspoke
Example data as received: -
[TABLE="width: 1000"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD="align: right"]16/08/2016 00:20:00[/TD]
[TD="align: right"]1.11818[/TD]
[TD="align: right"]1.11823[/TD]
[TD="align: right"]1.11818[/TD]
[TD="align: right"]1.11820[/TD]
[TD="align: right"]1.11816[/TD]
[TD="align: right"]1.11820[/TD]
[TD="align: right"]1.11816[/TD]
[TD="align: right"]1.11820[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:18:00[/TD]
[TD="align: right"]1.11834[/TD]
[TD="align: right"]1.11834[/TD]
[TD="align: right"]1.11811[/TD]
[TD="align: right"]1.11813[/TD]
[TD="align: right"]1.11829[/TD]
[TD="align: right"]1.11829[/TD]
[TD="align: right"]1.11807[/TD]
[TD="align: right"]1.11809[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:17:00[/TD]
[TD="align: right"]1.11831[/TD]
[TD="align: right"]1.11834[/TD]
[TD="align: right"]1.11827[/TD]
[TD="align: right"]1.11834[/TD]
[TD="align: right"]1.11827[/TD]
[TD="align: right"]1.11830[/TD]
[TD="align: right"]1.11824[/TD]
[TD="align: right"]1.11829[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:14:00[/TD]
[TD="align: right"]1.11838[/TD]
[TD="align: right"]1.11840[/TD]
[TD="align: right"]1.11826[/TD]
[TD="align: right"]1.11826[/TD]
[TD="align: right"]1.11835[/TD]
[TD="align: right"]1.11837[/TD]
[TD="align: right"]1.11822[/TD]
[TD="align: right"]1.11822[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:13:00[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11837[/TD]
[TD="align: right"]1.11838[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11834[/TD]
[TD="align: right"]1.11835[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:12:00[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11864[/TD]
[TD][/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:11:00[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11865[/TD]
[TD="align: right"]1.11863[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:10:00[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11866[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:09:00[/TD]
[TD="align: right"]1.11866[/TD]
[TD="align: right"]1.11866[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11861[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:08:00[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11866[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11866[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11861[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:07:00[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11855[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11855[/TD]
[TD="align: right"]1.11856[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:06:00[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11855[/TD]
[TD="align: right"]1.11855[/TD]
[TD="align: right"]1.11855[/TD]
[TD="align: right"]1.11855[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:04:00[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11857[/TD]
[TD][/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11855[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:03:00[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11859[/TD]
[TD][/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11855[/TD]
[TD="align: right"]1.11856[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016 00:02:00[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11857[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:57:00[/TD]
[TD="align: right"]1.11863[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11863[/TD]
[TD="align: right"]1.11863[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11857[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:56:00[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11866[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11863[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11858[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:55:00[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11864[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11859[/TD]
[TD][/TD]
[TD="align: right"]1.11859[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:54:00[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11857[/TD]
[TD][/TD]
[TD="align: right"]1.11854[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:53:00[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11855[/TD]
[TD="align: right"]1.11857[/TD]
[TD][/TD]
[TD="align: right"]1.11856[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:52:00[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11855[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:51:00[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11854[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:50:00[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11853[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11851[/TD]
[TD="align: right"]1.11854[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:48:00[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11856[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11855[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:47:00
[/TD]
[TD="align: right"]1.11862[/TD]
[TD][/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11855[/TD]
[TD="align: right"]1.11856[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:46:00[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11863[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11853[/TD]
[TD="align: right"]1.11859[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:45:00[/TD]
[TD="align: right"]1.11849[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11849[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11843[/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11843[/TD]
[TD="align: right"]1.11854[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:43:00[/TD]
[TD="align: right"]1.11865[/TD]
[TD="align: right"]1.11868[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11865[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11859[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:42:00[/TD]
[TD="align: right"]1.11865[/TD]
[TD="align: right"]1.11867[/TD]
[TD="align: right"]1.11860[/TD]
[TD="align: right"]1.11865[/TD]
[TD="align: right"]1.11859[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11862[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:41:00[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11868[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11865[/TD]
[TD="align: right"]1.11865[/TD]
[TD][/TD]
[TD="align: right"]1.11858[/TD]
[TD="align: right"]1.11859[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016 23:40:00[/TD]
[TD="align: right"]1.11870[/TD]
[TD="align: right"]1.11870[/TD]
[TD="align: right"]1.11857[/TD]
[TD="align: right"]1.11862[/TD]
[TD="align: right"]1.11861[/TD]
[TD="align: right"]1.11865[/TD]
[TD="align: right"]1.11854[/TD]
[TD="align: right"]1.11865[/TD]
[/TR]
</tbody>[/TABLE]