Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
2 dimensional date sort
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. ;( -- Walter Briscoe |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
2 dimensional date sort
Hi Walter,
Am Sun, 2 Dec 2012 12:44:18 +0000 schrieb Walter Briscoe: 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 select your rows = Data = Sort = Options = Sort Columns Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
2 dimensional date sort
On Sun, 2 Dec 2012 12:44:18 +0000, Walter Briscoe wrote:
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 =============================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort 2 dimensional Array, copy a row | Excel Programming | |||
Export 1-dimensional array values to a two-dimensional table? | Excel Programming | |||
Multi-column two-dimensional quick sort | Excel Programming | |||
Sort on two columns in two dimensional array | Excel Programming | |||
sort multi-dimensional array on numeric data? | Excel Programming |