reversing a list in Excel
I know how to sort ascending / descending
I have have a list that I need to invert, but keep the integrity of the list
5
6
10
11
9
4
3
to
3
4
9
10
11
6
5
How do I do this in MS Excel ? (my list is very long)
I have have a list that I need to invert, but keep the integrity of the list
5
6
10
11
9
4
3
to
3
4
9
10
11
6
5
How do I do this in MS Excel ? (my list is very long)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Column that you have just created by adding an identifier from 1 to x
ASKER
whats an identifier ?
Identifier = anything that enables identification. In this instance a number that indicates position.
See attached. Top section formulas, bottom section copy pasted values. I have done this as a separate list but I guess you would do the copy paste in place and sort the original list.
Thanks
Rob H
List-Integrity.xlsx
See attached. Top section formulas, bottom section copy pasted values. I have done this as a separate list but I guess you would do the copy paste in place and sort the original list.
Thanks
Rob H
List-Integrity.xlsx
ASKER
great thanks
Here's a macro. It assumes the data is in column A.
Sub ReverseList()
Dim DataRange As Variant ' Must be Variant
Dim Reversed() As String
Dim lngRow As Long
' This loads DateRange with the sheet's values. Note the use of ".Value"
With ActiveSheet
DataRange = .Range("A1:A" & .UsedRange.Rows.Count).value ' read all the values at once from the Excel grid, put into an array
ReDim Reversed(.UsedRange.Rows.Count - 1)
' Saved the values in revewrse order
For lngRow = 1 To UBound(DataRange)
Reversed(UBound(DataRange) - lngRow) = DataRange(lngRow, 1)
Next
' Replace the values
For lngRow = 0 To UBound(Reversed)
DataRange(lngRow + 1, 1) = Reversed(lngRow)
Next
' Update the sheet
.Range("A1:A" & .UsedRange.Rows.Count).value = DataRange ' writes all the results back to the range at once
End Wit
ASKER