Rod from Australia sends in an excellent tip for today's episode. Rod receives a CSV file every day that needs to be transposed. Rod put together an elegant combination of INDEX, COLUMN, and ROW to solve this problem. Episode 945 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! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're going to analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey, all right! Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, a tip this is sent in from Australia.
Rod send in this tip and he said, "You know, I saw this back in episodes 484, 45, 46.
I would never have a use for this in real life." But Rod came up with a great use for using index.
But Rod takes us to the next level.
I want to talk about a couple of functions, before we talk about the index function and introduce you to the problem.
I'm going to talk about the ROW function.
So, I'm just going to come here and say equal row and then open and closed parenthesis, press [ ctrl enter ] and you see that what the ROW function does, is it tells you, what row, that cell is in.
So, if you don't specify any reference in the ROW function...
You know, basically just give me the numbers 1 through 10 and if I would insert a new row, everything automatically updates.
So, it's a nice great way of seeing which row, you're in.
A similar function is the column function.
So, we'll just select some data here equal column [ control enter ] and it shows you which column, you're in.
Okay, alright! So, here's Rod's problem.
Every day he's getting a CSV file, from somewhere and whoever creates the CSV file is creating it wrong.
Alright! Because Rod needs the months going across, lines going down and even I know that for us to go back to whoever's creating that and say hey change your program.
It'll take months before they ever get to it.
So, everyday Rod was taking this data in doing an edit, paste special, transpose to turn it sideways.
But the problem is that, Rod then had a reformat the report at all kinds of color, patterns and things like that.
So, here was Rod's solution.
He used the index function, equal index and he said I want the index of this data back here on the CSV file.
Okay! Now, index is kind of obscure.
What we have to say now, is which Row we want and which column we want?
Well, what Rod did, is for the row information.
He asked for the current column and for the column information he asked for the current row.
Closing parenthesis there and when we copy this throughout, you'll see that it takes the data and transposes it perfectly.
But because we're doing it with a formula than any formatting that Rod does that formatting stays put.
So, all you have to do is paste the new data back into the CSV report.
These very cool index functions.
They use column and row automatically turn the data sideways.
It's a beautiful thing when you can use one formula and copy it throughout your report and it works everywhere.
I want to thank Rod for sending in this excellent tip.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're going to analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey, all right! Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, a tip this is sent in from Australia.
Rod send in this tip and he said, "You know, I saw this back in episodes 484, 45, 46.
I would never have a use for this in real life." But Rod came up with a great use for using index.
But Rod takes us to the next level.
I want to talk about a couple of functions, before we talk about the index function and introduce you to the problem.
I'm going to talk about the ROW function.
So, I'm just going to come here and say equal row and then open and closed parenthesis, press [ ctrl enter ] and you see that what the ROW function does, is it tells you, what row, that cell is in.
So, if you don't specify any reference in the ROW function...
You know, basically just give me the numbers 1 through 10 and if I would insert a new row, everything automatically updates.
So, it's a nice great way of seeing which row, you're in.
A similar function is the column function.
So, we'll just select some data here equal column [ control enter ] and it shows you which column, you're in.
Okay, alright! So, here's Rod's problem.
Every day he's getting a CSV file, from somewhere and whoever creates the CSV file is creating it wrong.
Alright! Because Rod needs the months going across, lines going down and even I know that for us to go back to whoever's creating that and say hey change your program.
It'll take months before they ever get to it.
So, everyday Rod was taking this data in doing an edit, paste special, transpose to turn it sideways.
But the problem is that, Rod then had a reformat the report at all kinds of color, patterns and things like that.
So, here was Rod's solution.
He used the index function, equal index and he said I want the index of this data back here on the CSV file.
Okay! Now, index is kind of obscure.
What we have to say now, is which Row we want and which column we want?
Well, what Rod did, is for the row information.
He asked for the current column and for the column information he asked for the current row.
Closing parenthesis there and when we copy this throughout, you'll see that it takes the data and transposes it perfectly.
But because we're doing it with a formula than any formatting that Rod does that formatting stays put.
So, all you have to do is paste the new data back into the CSV report.
These very cool index functions.
They use column and row automatically turn the data sideways.
It's a beautiful thing when you can use one formula and copy it throughout your report and it works everywhere.
I want to thank Rod for sending in this excellent tip.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
