Multiple date/time value alignments

[plusunim]

Hello.

I've been going through fora, but haven't found a satisfactory answer.

I have multiple column pairs (>80) of date/time-value data. Unfortunately the first date/time values are all of different beginnings. So one starts at noon, the other next pair starts at 12:05 etc. All have the same time interval.

The basic structure is:
Col.A Col.B Col.C Col.D ... ... Col.X Col.Y ...
R2 D-T Data D-T Data ... ...
R4 12:00 Val 12:15 Val ... ... 12:03 Val ... etc.

I know that I can manually sort this thing out, but it would take me ages to align and sort out. I was hoping to have a macro do the dirty job. I'm not very good at VBA, but a pseudocode that I have in mind could go:

1. scan the first line of date/time columns and find the earliest date/time value;
2. copy this column and column+1 to another worksheet in the next vacant column(s);
3. align the latest copied columns with the column-2 date/time (ie blank cells will be generated above this newly pasted data to match dates and times on the same row; data values move along with their respective date/times)
4. goto 1 until no columns are left in the original worksheet;

The number of pairs is known, as I use this in another macro for a for-loop. This value resides in A1 in the original worksheet.
R2 and R4 above are row2 and row4 respectively, D-T stands for date and time, formatted as dd/mm/yyyy hh:mm:ss

Thanks for any feedback provided.

 

[Pup Denab]

Hi plusunim
Welcome to the forum, it looks like, all you want to do, is aline the data, by date, you could do this in a multiple consolidation pivot table, your probably won't be able to see this in your tool-bar, as it was hidden around excel 2003, however if you open excel option, customise, then add this function "Pivot Table & Pivot Chart Wizard", it will take some getting used to but it will sort the data as you require.

 

[plusunim]

Thanks for the feedback. I'll try it out and will let you know.

 

[shg]

There's a workbook that may do what you want at https://app.box.com/shared/elrnbidnr7

 

[plusunim]

[SOLVED] Multiple date/time value alignments

shg..that worked wonders!
I'm still trying to understand how to implement the pivot-table idea...maybe for something else cropping into mind...

Cheers!

 

[shg]

Re: [SOLVED] Multiple date/time value alignments

You're welcome, glad it worked for you.

 

[plusunim]

Re: [SOLVED] Multiple date/time value alignments

Hi.

I'm not sure if you can help. The xlAlignKeys macro cannot run because of an error in loading a DLL. Does this macro use a particular reference library? It worked on a Excel2010. I'm getting this error on an Excel2003. Is there a particular newer library I need to load in 2003 from 2010?

I cannot even load the macro in the IDE to have a look. The same error pops up 'Error in loading DLL'

Thanks.