Solved

reversing a list in Excel

Posted on 2013-11-21
7
237 Views
Last Modified: 2013-11-21
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
Comment
Question by:MichaelGlancy
  • 3
  • 3
7 Comments
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
 

Author Comment

by:MichaelGlancy
Comment Utility
sort on new ID column ?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Column that you have just created by adding an identifier from 1 to x
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:MichaelGlancy
Comment Utility
whats an identifier ?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 

Author Closing Comment

by:MichaelGlancy
Comment Utility
great thanks
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now