In today's dueling podcast, how to turn a range upside down. Episode #1015 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! I'm Bill Jelen from MrExcel.com.
I've got a cool excel tip for you, today.
Hey! This is Mike Gel Girvin.
Excel Is Fun on YouTube and I have a different way to do that.
Hey! Welcome back, it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
Today's web question is sent by YouTube.
Someone has a range of data and they'd like to invert that range.
In other words, the last cell becomes the first cell and the first cell becomes the last cell.
I'm going to do this with a formula.
I'm gonna come over here and create a new range called inverted and I want to figure out how many rows I have in the original data set.
So, I'm going to use [ ctrl + shift + down arrow ] to select that and we see up here that it's 24 rows.
Now if you go further, it's going to become a tool tip down here at the bottom.
So, you can see I'm in rows but we have 24 rows need to know that number and I'm gonna put build a formula here equal INDEX.
The index function can handle an array.
In this case, it's a vector array and we just have to pass it the row number that we want.
So, I'm gonna say the index of this original range, I press the [ F4 ] key to lock that down and then I want 25 minus.
In this case I want 25 - 1 but I'm not going to put a 1 there, that would be horrible.
I would have to copy that over.
So, I'm gonna ask for the ROW of A1.
Row of A1 being a very geeky way to write the number one.
Will press [ enter ] and the first test we get is the last time down here X29 becomes the first item up here.
Now, let's copy that formula down and you see that it goes in our second to the last item W28, W28.
B21, B21 and the great thing about this is it is live.
So, if we would sort this the other way.
Let's go here to data and click [ sort descending ], you see that my list the formula here becomes absolutely inverted including the product in the wrong spot.
So, that's my formula method.
Let's throw it over to Mike and see what Mike from Excel Is Fun, has for solution.
Mike: Thanks, MrExcel.
Now, how is a great formula here I have to tell you.
If that row is geeky.
I'm going to do something that's even geekier.
Hey! I'm going to click right here, and I'm going to use the same IINDEX.
INDEX is such a versatile, amazing function and I'm going to get the same array.
I'll click right there [ ctrl + shift + down arrow ] on an [ F4 ] to lock it going down.
There is the array comma, now we need a row number.
Now, I'm going to use the ROWS function., right.
So that's at least one has geekier than row and what I'm going to do something to highlight this whole range here.
[ Ctrl +shift + down ] now, and I don't want to [ F4 ] because I want one of them relative.
This one is going to be relative but I'm going to lock this one.
So, I'll hit the [ F4 ] key, lock it in front of the row reference and what this does 26, A26 is locked but A3 is not.
So, as we copy it down Right now, it's going to give us 24 rows.
Three including three, all the way to 26 is 24.
But we get down to the next row this A3 become A4.
So, it'll be one less row.
So, ROWS counts all the rows whereas the ROW function counts just a particular row.
I'm going to close, parenthesisand by the way, I have to give credit to Duke of Scouts and the comment section at the Excel ls Fun site for this formula.
[ ctrl + enter ] and then I'm going to drag it down and just like that, we get the same inverted range.
Let's take a look here, see how this one went to A4 to A26.
So, now it's counting one fewer rows.
Both this formula, this little construction here and this one right here gives us 24, 23, 22, 21 not only that but if you were going to do this to the side.
So here's a MrExcel's formula use the COLUMN function A1 and if you're going to use this one you use the columns because since we're copying across columns we need to count or find out what column or in.
Alright!
We'll see you next trick.
MrExcel: Mike! That's brilliant.
ROWS instead of ROW, a great improvement because then you don't have to hard code the number of cells in the range and also first time I've ever really thought about using a relative as the top cell and absolute as the bottom cell.
Now, I've done the opposite all the time when I'm creating a running total but this was just gorgeous point for Mike.
Hey! Everyone out there thanks for stopping by, on behalf of Mike and myself.
See you next time for another dueling podcast from MrExcel and Excel Is Fun.
I've got a cool excel tip for you, today.
Hey! This is Mike Gel Girvin.
Excel Is Fun on YouTube and I have a different way to do that.
Hey! Welcome back, it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
Today's web question is sent by YouTube.
Someone has a range of data and they'd like to invert that range.
In other words, the last cell becomes the first cell and the first cell becomes the last cell.
I'm going to do this with a formula.
I'm gonna come over here and create a new range called inverted and I want to figure out how many rows I have in the original data set.
So, I'm going to use [ ctrl + shift + down arrow ] to select that and we see up here that it's 24 rows.
Now if you go further, it's going to become a tool tip down here at the bottom.
So, you can see I'm in rows but we have 24 rows need to know that number and I'm gonna put build a formula here equal INDEX.
The index function can handle an array.
In this case, it's a vector array and we just have to pass it the row number that we want.
So, I'm gonna say the index of this original range, I press the [ F4 ] key to lock that down and then I want 25 minus.
In this case I want 25 - 1 but I'm not going to put a 1 there, that would be horrible.
I would have to copy that over.
So, I'm gonna ask for the ROW of A1.
Row of A1 being a very geeky way to write the number one.
Will press [ enter ] and the first test we get is the last time down here X29 becomes the first item up here.
Now, let's copy that formula down and you see that it goes in our second to the last item W28, W28.
B21, B21 and the great thing about this is it is live.
So, if we would sort this the other way.
Let's go here to data and click [ sort descending ], you see that my list the formula here becomes absolutely inverted including the product in the wrong spot.
So, that's my formula method.
Let's throw it over to Mike and see what Mike from Excel Is Fun, has for solution.
Mike: Thanks, MrExcel.
Now, how is a great formula here I have to tell you.
If that row is geeky.
I'm going to do something that's even geekier.
Hey! I'm going to click right here, and I'm going to use the same IINDEX.
INDEX is such a versatile, amazing function and I'm going to get the same array.
I'll click right there [ ctrl + shift + down arrow ] on an [ F4 ] to lock it going down.
There is the array comma, now we need a row number.
Now, I'm going to use the ROWS function., right.
So that's at least one has geekier than row and what I'm going to do something to highlight this whole range here.
[ Ctrl +shift + down ] now, and I don't want to [ F4 ] because I want one of them relative.
This one is going to be relative but I'm going to lock this one.
So, I'll hit the [ F4 ] key, lock it in front of the row reference and what this does 26, A26 is locked but A3 is not.
So, as we copy it down Right now, it's going to give us 24 rows.
Three including three, all the way to 26 is 24.
But we get down to the next row this A3 become A4.
So, it'll be one less row.
So, ROWS counts all the rows whereas the ROW function counts just a particular row.
I'm going to close, parenthesisand by the way, I have to give credit to Duke of Scouts and the comment section at the Excel ls Fun site for this formula.
[ ctrl + enter ] and then I'm going to drag it down and just like that, we get the same inverted range.
Let's take a look here, see how this one went to A4 to A26.
So, now it's counting one fewer rows.
Both this formula, this little construction here and this one right here gives us 24, 23, 22, 21 not only that but if you were going to do this to the side.
So here's a MrExcel's formula use the COLUMN function A1 and if you're going to use this one you use the columns because since we're copying across columns we need to count or find out what column or in.
Alright!
We'll see you next trick.
MrExcel: Mike! That's brilliant.
ROWS instead of ROW, a great improvement because then you don't have to hard code the number of cells in the range and also first time I've ever really thought about using a relative as the top cell and absolute as the bottom cell.
Now, I've done the opposite all the time when I'm creating a running total but this was just gorgeous point for Mike.
Hey! Everyone out there thanks for stopping by, on behalf of Mike and myself.
See you next time for another dueling podcast from MrExcel and Excel Is Fun.
