Hey all right, welcome back to the Mr. Excel net cast. I’m Bill Jelen. Today a tip is, this is sent in from Australia, Rod sending this tip and he said, “You know I saw this back in 484, 485, 486 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 the problem. I want to talk about the row function, so I’m just going to come here and say, equal row and then open and close parentheses, press control Alt R, and you see to 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 it and you basically just give me the numbers one through 10, and if I would insert a new row, everything automatically updates, so it's nice great way of saying which row you’re in.
A similar function is the column function, so just like some data here you column, control letter and it shows you which column you’re in okay. Alright, so here’s Rod’s problem everyday. He is getting a CSV file from somewhere and whoever creates the CSV file is creating it wrong right? Because Rod needs the months going across lines going down and you and I know that for us to go back to whoever creating then say, “Hey, change your program for it will take months before they ever get to it.”
So everyday Rod was taking this data and doing it edit, paste special transpose to turn it sideways, but the promise is that Rod then had a reformat, report it all. All kinds of color, patterns and things like that.
So here is Rod’s solution, he used index function, equal index and he said, “I want the index of this data back here in the CSV file. An 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 formation, he asked for the current column. And for the column information, he asked for the current row. All closing parenthesis there there, and when we copy this throughout you'll see that it takes the data and transposed it perfectly, but because we're doing with the formula then any formatting that Rod does, that formatting stays put.
So all we have to do is paste the new data back into the CSV report this very cool index functions that use column and row. Automatically turn the data sideways. It's a beautiful thing where 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. I'll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services