Solved

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

Posted on 2014-02-13
195 Views
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
Question by:capterdi

LVL 43

Expert Comment

What's wrong with simply sorting on column A?
0

LVL 19

Expert Comment

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

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))
``````

Then format the column B as MM/DD/YYYY HH:MM:SS and sort by column B.
0

LVL 12

Expert Comment

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
Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:I")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
``````
0

Author Comment

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

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

Harry Lee earned 500 total points
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
Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:I")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
``````
0

Author Closing Comment

Harry,

Good job. Now it works.

Thanks a lot.
0

## Join & Write a Comment Already a member? Login.

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!