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
==========================================
