Solved

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

Posted on 2014-02-13
8
200 Views
Last Modified: 2014-02-13
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
Comment
Question by:capterdi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39856999
What's wrong with simply sorting on column A?
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39857026
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39857060
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
Independent Software Vendors: 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!

 
LVL 12

Expert Comment

by:Harry Lee
ID: 39857193
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
 

Author Comment

by:capterdi
ID: 39857292
Harry,

I prefer VBA.

Sorry.

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

TimeV=Mid(.....
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39857349
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
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39857374
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
 

Author Closing Comment

by:capterdi
ID: 39857530
Harry,

Good job. Now it works.

Thanks a lot.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question