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)
Europa MacDonaldChief slayer of dragonsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
In the next column over, add an identifier starting at 1 and incrementing by 1 as going down the column.

Then highlight both columns (and other columns if related) and sort on the new ID column but using Large to Small.

Thanks
Rob H
0
 
Europa MacDonaldChief slayer of dragonsAuthor Commented:
sort on new ID column ?
0
 
Rob HensonFinance AnalystCommented:
Column that you have just created by adding an identifier from 1 to x
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Europa MacDonaldChief slayer of dragonsAuthor Commented:
whats an identifier ?
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Europa MacDonaldChief slayer of dragonsAuthor Commented:
great thanks
0
 
Martin LissOlder than dirtCommented:
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

Open in new window

h
0
All Courses

From novice to tech pro — start learning today.