Separating rows based on data

[supernova2]

Hi there,

Firstly thanks for even looking at the topic. I've had a lot of trouble trying to work out how to do this and am looking for any sort of solution if possible.

Ive currently got data that looks a bit like this:

Time Stamp_________Other Info
2008-04-17 16:53:57__Etc
2008-04-18 11:15:00__Etc
2008-04-18 13:36:31__Etc
2008-04-18 13:38:38__Etc
2008-04-18 13:55:07__Etc
2008-04-18 15:21:15__Etc

And I was wondering what would be the easiest way of getting it into this sort of format.

Date_______Hour__Timestamp_________Other Info
17/04/2008__9_____Blank______________Blank
17/04/2008__10____Blank______________Blank
17/04/2008__11____Blank______________Blank
17/04/2008__12____Blank______________Blank
17/04/2008__13____Blank______________Blank
17/04/2008__14____Blank______________Blank
17/04/2008__15____Blank______________Blank
17/04/2008__16____2008-04-17 16:53:57__Etc
18/04/2008__9_____Blank______________Blank
18/04/2008__10____Blank ______________Blank
18/04/2008__11____2008-04-18 11:15:00__Etc
18/04/2008__12____Blank______________Blank
18/04/2008__13____2008-04-18 13:36:31__Etc
18/04/2008__13____2008-04-18 13:38:38__Etc
18/04/2008__13____2008-04-18 13:55:07__Etc
18/04/2008__14____Blank______________Blank
18/04/2008__15____2008-04-18 15:21:15__Etc
18/04/2008__16____Blank______________Blank

Sorry if im bad at explaining it. Basically I want excel to create a table with all the values possible separated by the date and hour and then sort the data that I have into it. If there are multiple entries within an hour these need to be shown and if there is no data for that hour it needs to indicate that there are no entries.

Any ideas at all?

edit: its getting the multiple entries in when I pull the information across that getting my noodle. I can do a pivot table but it wont contain all the blanks.

 

[Richard U]

Hi there,

Firstly thanks for even looking at the topic. I've had a lot of trouble trying to work out how to do this and am looking for any sort of solution if possible.

Ive currently got data that looks a bit like this:

Time Stamp_________Other Info
2008-04-17 16:53:57__Etc
2008-04-18 11:15:00__Etc
2008-04-18 13:36:31__Etc
2008-04-18 13:38:38__Etc
2008-04-18 13:55:07__Etc
2008-04-18 15:21:15__Etc

And I was wondering what would be the easiest way of getting it into this sort of format.

Date_______Hour__Timestamp_________Other Info
17/04/2008__9_____Blank______________Blank
17/04/2008__10____Blank______________Blank
17/04/2008__11____Blank______________Blank
17/04/2008__12____Blank______________Blank
17/04/2008__13____Blank______________Blank
17/04/2008__14____Blank______________Blank
17/04/2008__15____Blank______________Blank
17/04/2008__16____2008-04-17 16:53:57__Etc
18/04/2008__9_____Blank______________Blank
18/04/2008__10____Blank ______________Blank
18/04/2008__11____2008-04-18 11:15:00__Etc
18/04/2008__12____Blank______________Blank
18/04/2008__13____2008-04-18 13:36:31__Etc
18/04/2008__13____2008-04-18 13:38:38__Etc
18/04/2008__13____2008-04-18 13:55:07__Etc
18/04/2008__14____Blank______________Blank
18/04/2008__15____2008-04-18 15:21:15__Etc
18/04/2008__16____Blank______________Blank

Sorry if im bad at explaining it. Basically I want excel to create a table with all the values possible separated by the date and hour and then sort the data that I have into it. If there are multiple entries within an hour these need to be shown and if there is no data for that hour it needs to indicate that there are no entries.

Any ideas at all?

edit: its getting the multiple entries in when I pull the information across that getting my noodle. I can do a pivot table but it wont contain all the blanks.

Use the SPLIT function in VBA to parse out your data. You may have to do multiple splits to do this. From there you can either use the resulting array, or feed each piece of data into individual variables to format them before reposting to the spreadsheet.

Does that help?