I use Excel 2003.
I have a file, with about 1500 sets of dates in adjacent cells in rows.
I would like to sort that data. I suspect I am going to have to do so in
VBA, but would prefer worksheet instructions.
This is the rendering of a couple of rows of that data:
06-Nov-12 04-Dec-12 08-Jan-13 05-Feb-13 05-Mar-13 01-Oct-13
10-Nov-12 08-Dec-12 09-Feb-13 09-Mar-13 12-Oct-13
I want the output in month order. e.g. January - December, ignoring year
and day. So the output from those rows would be
08-Jan-13 05-Feb-13 05-Mar-13 01-Oct-13 06-Nov-12 04-Dec-12
09-Feb-13 09-Mar-13 12-Oct-13 10-Nov-12 08-Dec-12
I don't even know how to sort data in rows rather than in columns. ;(
Sorting horizontally should be easy as one of the sort options, even in Excel 2003 if I recall correctly, is to sort "left to right".
However, there are two issues:
1. You want to sort by month and ignore the year. There is no built-in way to do that.
2. It appears from your example that you want to sort each row independently of the others.
One method without using VBA would be:
Insert blank row after every data row
Extract the month into that row ( e.g.: A2: =month(a1) and fill right)
Select each pair of rows -- on pair at a time
Sort by the "2nd row" ascending
delete all the "2nd rows".
Needless to say, a VBA solution would be quicker with 1500 rows to process.
Here's one way to do it:
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
NOTE: The macro has hardcoded the location of your data source as being on "Sheet1" and starting in A1 with no header rows.
The macro will place the results of the sort on "Sheet2". Any other information on Sheet2 will be destroyed.
If Sheet2 does not exist, an error will result.
All of these parameters can be modified if the code generally does what you expect. But you should initially run the code on a copy of your workbook that has the worksheets and data set up as expected. Then you can provide the detail required to make it run better.
====================================
Option Explicit
Sub SortDateRowsByMonth()
Dim v1 As Variant, v2() As Variant
Dim vRes() As Variant
Dim rSrc As Range, rRes As Range
Dim wsSrc As Worksheet, wsRes As Worksheet
Dim i As Long, j As Long, k As Long
Set wsSrc = Worksheets("sheet1")
Set wsRes = Worksheets("Sheet2")
wsRes.Cells.Clear
Set rRes = wsRes.Range("A1")
With wsSrc
Set rSrc = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
ReDim vRes(1 To rSrc.Count, 1 To 1)
With wsSrc
For i = 1 To rSrc.Count
v1 = Range(rSrc(i), .Cells(i, .Columns.Count).End(xlToLeft))
ReDim v2(1 To 2, 1 To UBound(v1, 2))
For j = 1 To UBound(v2, 2)
v2(1, j) = v1(1, j)
v2(2, j) = Month(v1(1, j))
Next j
k = UBound(v2, 2)
If k > UBound(vRes, 2) Then ReDim Preserve vRes(1 To UBound(vRes, 1), 1 To k)
MyQuickSort_Single v2, LBound(v2, 2), UBound(v2, 2), 2, True
For j = 1 To UBound(v2, 2)
vRes(i, j) = v2(1, j)
Next j
Next i
End With
Set rRes = rRes.Resize(rowsize:=UBound(vRes, 1), columnsize:=UBound(vRes, 2))
rRes.NumberFormat = rSrc(1).NumberFormat
rRes = vRes
rRes.EntireColumn.AutoFit
End Sub
'-----------------------------------------
Sub MyQuickSort_Single(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long, _
ByVal PrimeSort As Integer, ByVal Ascending As Boolean)
Dim Low As Long, High As Long
Dim Temp As Variant, List_Separator1 As Variant
Dim i As Long
Dim TempArray() As Variant
ReDim TempArray(UBound(SortArray, 1))
Low = First
High = Last
List_Separator1 = SortArray(PrimeSort, (First + Last) / 2)
Do
If Ascending = True Then
Do While (SortArray(PrimeSort, Low) < List_Separator1)
Low = Low + 1
Loop
Do While (SortArray(PrimeSort, High) > List_Separator1)
High = High - 1
Loop
Else
Do While (SortArray(PrimeSort, Low) > List_Separator1)
Low = Low + 1
Loop
Do While (SortArray(PrimeSort, High) < List_Separator1)
High = High - 1
Loop
End If
If (Low <= High) Then
For i = LBound(SortArray, 1) To UBound(SortArray, 1)
TempArray(i) = SortArray(i, Low)
Next
For i = LBound(SortArray, 1) To UBound(SortArray, 1)
SortArray(i, Low) = SortArray(i, High)
Next
For i = LBound(SortArray, 1) To UBound(SortArray, 1)
SortArray(i, High) = TempArray(i)
Next
Low = Low + 1
High = High - 1
End If
Loop While (Low <= High)
If (First < High) Then MyQuickSort_Single SortArray, First, High, PrimeSort, Ascending
If (Low < Last) Then MyQuickSort_Single SortArray, Low, Last, PrimeSort, Ascending
End Sub
===============================
