Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sort by Every Other Row?
Hi -
I'm using Excel 2010. I have several long lists (each is 600+) of vocabulary words & definitions and need to alphabetize/sort the words, but need to keep the definitions w/ the words. A1 = word 1 A2 = def. 1 A3 = word 2 A4 = def. 2 A5 = word 3 A6 = def. 3 Standard sort will alphabetize all rows & the words will be separated from the definitions. I could group rows 1 & 2 together, rows 3 & 4 together, etc. but that's clunky & time-consuming. Any advice? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by Every Other Row?
On Wed, 11 Jul 2012 19:35:20 +0000, KeithS. wrote:
Hi - I'm using Excel 2010. I have several long lists (each is 600+) of vocabulary words & definitions and need to alphabetize/sort the words, but need to keep the definitions w/ the words. A1 = word 1 A2 = def. 1 A3 = word 2 A4 = def. 2 A5 = word 3 A6 = def. 3 Standard sort will alphabetize all rows & the words will be separated from the definitions. I could group rows 1 & 2 together, rows 3 & 4 together, etc. but that's clunky & time-consuming. Any advice? Thanks! You could do this with VBA, and still preserve that order. But is seems to me it would be simpler to re-arrange your data so that the words are in column 1, and the definitions in column 2 (which could also be done using vba, if necessary). If rearranging the data is not possible, then try this macro. Note that you MUST select the range to sort first. And also, the results are placed in column D. Once you are satisfied it works properly, you can replace the original merely by changing rDest in the code. 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), FIRST select the range of cells you wish to sort. Be sure that a "word" is in the first row; and a "definition" in the last row. Then <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. =================================== Option Explicit Sub SortEveryOther() Dim rSrc As Range Dim vSrc() As String, vRes() As String Dim rDest As Range Dim i As Long Set rSrc = Selection Set rDest = Range("D1") ReDim vSrc(0 To 1, 0 To rSrc.Count / 2 - 1) For i = 0 To UBound(vSrc, 2) vSrc(0, i) = rSrc(i * 2 + 1, 1) vSrc(1, i) = rSrc(i * 2 + 2, 1) Next i MyQuickSort_Single vSrc, LBound(vSrc, 2), UBound(vSrc, 2), 1, False ReDim vRes(0 To rSrc.Count - 1, 0 To 0) For i = 0 To UBound(vSrc, 2) vRes(i * 2, 0) = vSrc(0, i) vRes(i * 2 + 1, 0) = vSrc(1, i) Next i Set rDest = rDest.Resize(rowsize:=UBound(vRes) + 1) rDest.EntireColumn.ClearContents rDest = vRes 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 | |||
Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
sort (on part of) string - originally posted under Tricky Sort | Excel Programming |