• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

Sorting of data similar to Q_27960008 but with an additional complication.

Hi EE,

This case of data sorting poses an additional complication as compared to Q_27960008. Here I need to sort data according to first column of data. The format here is as follows:

26.01.2012 10:18:43.000

It means:

dd.mm.yyyy hh:mm:ss.000 (after second a decimal point followed by three zeros).

So I need to sort the data on the workbook precisely in a chronological order.

See attached sample file.

Thanks.
Book1.xls
0
capterdi
Asked:
capterdi
1 Solution
 
Saqib Husain, SyedEngineerCommented:
What's wrong with simply sorting on column A?
0
 
Ken ButtersCommented:
If you want a similar solution to Q_27960008..

then you can use this formula:

Supposing your data starts in Cell A1

Put this in new column --- and fill down as necessary
=CONCATENATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2),RIGHT(A1,12))

Then sort on new column, and then Hide or delete the new column.
0
 
Harry LeeCommented:
Saqib Husain, Syed,

The problem is obvious. The date and time in column A is a text string, and the format is DD.MM.YYYY HH:MM:SS

When sorting that columns, it's all being treated as text, and end up sorting using A-Z, 0-9.

Capterdi, do you prefer to use VBA or do you prefer to use formulas?

to use formula, add a column after the date column.

put formula in the new Column B starting B2
=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+TIME(MID(A2,12,2),MID(A2,15,2),RIGHT(A2,6))

Open in new window


Then format the column B as MM/DD/YYYY HH:MM:SS and sort by column B.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Harry LeeCommented:
To use VBA,

Sub correctDateandSort()
RwCnt = Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 1), Cells(RwCnt, 1)).Select
Selection.NumberFormat = "mm/dd/yyyy HH:mm:ss"

For i = 2 To RwCnt
    DateV = DateSerial(Mid(Cells(i, 1), 7, 4), Mid(Cells(i, 1), 4, 2), Left(Cells(i, 1), 2))
    TimeV = Mid(Cells(i, 1), 12, 2) / 24 + Mid(Cells(i, 1), 15, 2) / 24 / 60 + Right(Cells(i, 1), 6) / 24 / 60 / 60
    DTV = DateV + TimeV
    Cells(i, 1) = DTV
Next

Columns("A:I").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:= _
    Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveSheet.Sort
    .SetRange Range("A:I")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A1").Select
End Sub

Open in new window

0
 
capterdiAuthor Commented:
Harry,

I prefer VBA.

Sorry.

I run a test. I´m getting a run-time error '13' error on line

TimeV=Mid(.....
0
 
Harry LeeCommented:
capterdi,

I think I know your problem. You are trying to run the macro with already formatted column A.

Run the macro only on the raw data, just like your uploaded file.
0
 
Harry LeeCommented:
capterdi,

I have added a cell content test to minimize the chance of runtime error 13.

Sub correctDateandSort()
Dim RwCnt As Long, DTV As Date, DateV As Date, TimeV As Double, i As Long
RwCnt = Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 1), Cells(RwCnt, 1)).Select
Selection.NumberFormat = "mm/dd/yyyy HH:mm:ss"

For i = 2 To RwCnt
    If Not IsDate(Cells(i, 1)) Then
    DateV = DateSerial(Mid(Cells(i, 1), 7, 4), Mid(Cells(i, 1), 4, 2), Left(Cells(i, 1), 2))
    TimeV = Mid(Cells(i, 1), 12, 2) / 24 + Mid(Cells(i, 1), 15, 2) / 24 / 60 + Right(Cells(i, 1), 6) / 24 / 60 / 60
    DTV = DateV + TimeV
    Cells(i, 1) = DTV
    End If
Next

Columns("A:I").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:= _
    Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveSheet.Sort
    .SetRange Range("A:I")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A1").Select
End Sub

Open in new window

0
 
capterdiAuthor Commented:
Harry,

Good job. Now it works.

Thanks a lot.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now