I'm no Tiger Woods, but I get my share of lady fan mail. Today, I get an email from Dawn.
She asks: "Do you have an easy way to flip a range so that the bottom row is at the top and the top row is at the bottom?"
I have two methods for doing this.
Method 1: The Cheap Sorting Flip
If I need to do a one-time flip, I simply number my rows, and then sort in Descending order.
Method 2: The Sophisticated Formula Flip
If I need a more sustained way to flip a range, I use a formula.
The formula I typically use is: INDEX($B$2:$B$7,ROWS(B2:$B$7))
As you can see, this formula uses the INDEX function and the ROWS function.
The INDEX function translates a position number into a value. In this example, if I were to enter
INDEX($B$2:$B$7, 6), I would get "Mia" because that is the value in the sixth position in the range.
The trick is to figure out how to get the correct position numbers. This is where the ROWS function comes in.
The ROWS function tells you how many rows are in a given range. In this example,
ROWS(B2:B7) would return 6 because there are six rows in my selected range.
All I need to do is make sure that the range for the INDEX function remains locked, while the range for the ROWS function shrinks as I copy the formula down.
I do this by placing dollar signs on all references except the starting reference for the ROWS function – as in
ROWS(B2:$B$7).
This allows the ROWS function to return decreasing row numbers as I copy the formula down, essentially feeding the INDEX function the needed position numbers in backwards order.
If I need to apply this trick horizontally, I just use the COLUMNS function to do the same thing horizontally.
You can use a similar method to sort text in a range of cells
For Text
Index($B$2:$B$7,Match(Counta($B$2:B2),Countif($B$7:$B$7,"<="&$B$2:$B$7),0))
For numbers its simpler
=Small($B$2:$B$7,Counta($B$2:B2))