Thursday, June 19, 2014

Reversing number values

The Problem

This week's question to Allen at ExcelTips was from a person who wanted to reverse the values in a list of numbers ranging from 1 to 5: 5 becomes 1, 4 becomes 2, 3 stays 3, 2 becomes 4, and 5 becomes 1.  This problem is seen by college admins and high school guidance counselors because in some schools an A is 5 (or 4), in others it is 1.

There are a number of ways to do this.  This is what I submitted to Allen (with some edits, of course).

The Worksheet Method(s)

First, is the worksheet method, and there are two choices.
Assumption:  The 1-5 data starts in A2
  1. Insert a column (B) to the right of the column with the 1-5 data
  2. In B2 enter "=CHOOSE(A2,5,4,3,2,1)"  or "=VALUE(MID("54321",A2,1))"
  3. Copy/Fill that formula down
  4. Copy Column B results and Paste Value into column A
  5. Delete column A

The VBA Subs

The second formula is the basis for the following subroutine after selecting the column

Sub ReverseIntegers()    
    Dim i As Double
    On Error Resume Next ' This allows cells with text, like a header row to be ignored
    For i = 1 To Selection.Cells.Count
        Selection.Cells(i).Value = CInt(Mid("54321", Selection.Cells(i).Value, 1))
    Next i
    On Error GoTo 0
End Sub

Adapting the CHOOSE formula directly would result in a Select Case structure but the Instr function could also be used:

Sub ReverseIntegers2()    
    Dim i As Double
    On Error Resume Next ' This allows cells with text, like a header row to be ignored
    For i = 1 To Selection.Cells.Count
        Selection.Cells(i).Value = CInt(InStr(1, "54321", CStr(Selection.Cells(i).Value)))
    Next i
    On Error GoTo 0
End Sub