Hi,

I get info coming in that is formatted :-
08-Aug 02:42
08-Aug 07:29 08:10 09:58 10:45 11:58 13:33
09-Aug 04:57
09-Aug 06:28 09:46 10:32 11:28 12:44 13:44

This goes on for the entire month. I need it formatted as:-
08-Aug 02:42
08-Aug 07:29
08-Aug 08:10
08-Aug 09:58
08-Aug 10:45
08-Aug 11:58

Etc.
Anyone got any ideas of a quick way of doing this?

select the first 2 columns and then Paste Special--Transpose will do it

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Buyone" wrote:

Hi,

I get info coming in that is formatted :-
08-Aug 02:42
08-Aug 07:29 08:10 09:58 10:45 11:58 13:33
09-Aug 04:57
09-Aug 06:28 09:46 10:32 11:28 12:44 13:44

This goes on for the entire month. I need it formatted as:-
08-Aug 02:42
08-Aug 07:29
08-Aug 08:10
08-Aug 09:58
08-Aug 10:45
08-Aug 11:58

Etc.
Anyone got any ideas of a quick way of doing this?

As illustrated at John Walkenbach's website :
http://j-walk.com/ss/excel/usertips/tip068.htm

If your data is actually in separate cells....like this:
Date___Time_1_____Time_2____Time_3____Time_4____Ti me_5____Time_6
8-Aug____2:42____(blank)___(blank)___(blank)___(blan k)___(blank)
8-Aug____7:29_______8:10______9:58_____10:45_____11: 58_____13:33
9-Aug____4:57____(blank)___(blank)___(blank)___(blan k)___(blank)
9-Aug____6:28_______9:46_____10:32_____11:28_____12: 44_____13:44

<Data<Pivot Table
Use: Multiple Consolidation Ranges_____Click [Next]
Select: "I will create the page fields"_____Click [Next]
Range: (Select your data)_____Click [Add]_____Click [Next]

Click the [Layout] button
ROW: Drag ROW off the diagram
COLUMN: Drag COLUMN off the diagram
DATA: Leave the VALUE field in this section
Click the [OK] button

Select a location for the Pivot Table_____Click [Finish]

That will create a minimal Pivot Table containing only one cell with a
value.

Double-Click on that one value cell
Excel will add a sheet to the workbook with the details of
that cell in a database table format, like this:
Row___________Column______Value
8/8/2007______Time_1______2:42:00 AM
8/8/2007______Time_2______(blank)
8/8/2007______Time_3______(blank)
8/8/2007______Time_4______(blank)
8/8/2007______Time_5______(blank)
8/8/2007______Time_6______(blank)
8/8/2007______Time_1______7:29:00 AM
8/8/2007______Time_2______8:10:00 AM
8/8/2007______Time_3______9:58:00 AM
8/8/2007______Time_4______10:45:00 AM
8/8/2007______Time_5______11:58:00 AM
8/8/2007______Time_6______1:33:00 PM
8/9/2007______Time_1______4:57:00 AM
8/9/2007______Time_2______(blank)
8/9/2007______Time_3______(blank)
8/9/2007______Time_4______(blank)
8/9/2007______Time_5______(blank)
8/9/2007______Time_6______(blank)
8/9/2007______Time_1______6:28:00 AM
8/9/2007______Time_2______9:46:00 AM
8/9/2007______Time_3______10:32:00 AM
8/9/2007______Time_4______11:28:00 AM
8/9/2007______Time_5______12:44:00 PM
8/9/2007______Time_6______1:44:00 PM

Then just delete the blank rows.
Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Buyone" wrote in message
...
Hi,

I get info coming in that is formatted :-
08-Aug 02:42
08-Aug 07:29 08:10 09:58 10:45 11:58 13:33
09-Aug 04:57
09-Aug 06:28 09:46 10:32 11:28 12:44 13:44

This goes on for the entire month. I need it formatted as:-
08-Aug 02:42
08-Aug 07:29
08-Aug 08:10
08-Aug 09:58
08-Aug 10:45
08-Aug 11:58

Etc.
Anyone got any ideas of a quick way of doing this?

This is an old routine that I use to do this. I really need to
rewrite it, but it gets the job done. Be sure to test this on a copy
of your data before running it on your main data. Better safe than
sorry.
Sub SplitDups()
DupCol = InputBox("Seperate duplicates based on which column?", _
"Column Entry", ColumnLetter(ActiveCell.Column))
If DupCol = "" Then Exit Sub
Range(DupCol & "1").Name = "SortCol"
BotRow = Range("SortCol").End(xlDown).Row - 1
If ActiveCell = "" Then
MsgBox "You must be on the data you want sorted."
Exit Sub
End If
i = Range("IV1").End(xlToLeft).Column - Range("SortCol").Column
x = 1
Do
For y = 2 To i
If Trim(Range("SortCol").Offset(x, y)) < "" Then
Rows(x + 1).Copy
Rows(x + y).Insert Shift:=xlDown
Range("SortCol").Offset(x + y - 1, 1) = _
Range("SortCol").Offset(x, y)
j = j + 1
BotRow = BotRow + 1
End If
Next
x = x + 1 + j
j = 0
Loop Until x BotRow
Range(Range("SortCol").Offset(0, 2), _
Range("SortCol").Offset(0, i)).EntireColumn.Delete
End Sub
Buyone wrote:
Hi,

I get info coming in that is formatted :-
08-Aug 02:42
08-Aug 07:29 08:10 09:58 10:45 11:58 13:33
09-Aug 04:57
09-Aug 06:28 09:46 10:32 11:28 12:44 13:44

This goes on for the entire month. I need it formatted as:-
08-Aug 02:42
08-Aug 07:29
08-Aug 08:10
08-Aug 09:58
08-Aug 10:45
08-Aug 11:58

Etc.
Anyone got any ideas of a quick way of doing this?