Although I've managed to create some simple macros successfully, the problem I have at present seems beyond my abilities, so I'd be grateful for some pointers please!! - does a A VBA script already exist that will do this?

I have series of files with 4 long data columns:

Identifier         Date         Time      Value 

 123456          dd/mm/yyyy     hh:mm     12.3   

They contain data from 15 days recorded every 15 minutes

I wish to create a macro to automate the process of re-tabulating the values from column 4 into a table sorted by date and time with 15 columns (one for each day) and 96 rows (one for each 15 minutes of the 24hr day) so that

any value recorded between midnight and 00:14 appears in row 1,
00:15 - 00:29 in row 2 etc etc

There are occasional data gaps and for these cells in the table I'd like a blank to be recorded

  • Have you tried a pivot table? – OldUgly Feb 11 '17 at 23:22
  • 1
    Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?. – DavidPostill Feb 11 '17 at 23:29
  • Apologies if I've asked an inappropriate question - I do not know VBA so have used macro recorder to create macros by recording mouse-click actions - however for this particular problem, whilst it seems something which should be relatively simple in theory I can't seem to get the process started as just creating the format of the table from the dates / times (which can vary slightly from file to file) seems beyond the filtering process with a mouse-click – I. Cranston Feb 12 '17 at 0:12
  • Recording the macro is a good start, but replace the Select with Value and set it to a variable that you can use. Just loop through the dates and when it changes, add 4 (or 5) to columns and keep copying values. If you need the script to be faster, try moving/copying data in bulk. If you have a specific problem with your script, please update your question. – reasra Feb 13 '17 at 19:33

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.