• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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)
0
Europa MacDonald
Asked:
Europa MacDonald
  • 3
  • 3
1 Solution
 
Rob HensonFinance 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now