Multiple date/time value alignments

plusunim

New Member
Joined
Aug 17, 2014
Messages
15
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
[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!
 
Upvote 0
Re: [SOLVED] Multiple date/time value alignments

You're welcome, glad it worked for you.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top