Ryan has a data set where all of the fields are going down column A and he would like to take this column of data back into a rectangular range. Normally, I would use a macro to do this, but today, a single INDEX formula copied through the range does the trick.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1890.
Unwind Data Using INDEX.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question posted by Ryan at one of my Power Excel Seminars.
He has all of these data going down Column A, these are really fields, like you want to have the name in column C, the street in column D, the city, state, zip in column E.
So, most of the time when I have this problem, well, Sylvia what do you think of it?
That's right, of course, usually I use a macro to do this.
This is the classic example I always use for my macros, but I say, you know, there's a formula that will do this as well.
So we have to count on the fact here, that there are exactly 4 cells for every record.
First record starts in row 1, next record starts a row 5, kind of put that pattern in there and we'll grab the fill handle and drag to get far enough down to the end of the data set.
I guess, I should figure out where the end of the data set was.
We go down to 489, let's take a look if I got far enough down.
No, I did not, so we'll keep dragging some more, it's like both cells that gives it the pattern, there we go, 49, too far.
Alright, so once we have those numbers going down, then across the top of it put the number 0, 1 and 2.
All right, so what we're going to do then, is we're going to start here and do =INDEX($A:$A), now I need to press F4 to make sure that as I copy this formula to the right, it doesn't change to column B and column C. And then it wants to know what row we want, all right.
We want to add two numbers together.
Whatever number is back here in column B, so B2, but I need to lock that down.
Not with both dollar signs, but just with a single dollar sign, before the B. So that's F4, I have to press F4 three times… 2, 3 times, like that, to put “$B2”.
That says: hey, we're always going to point to B, but as we drag it across, it's not going to change from B, but the 2 is allowed to change to 3, 4, 5, 6 as we go down.
And then we're going to add to that the column incrementer, that's up in C1, we’ll press F4, just twice there to lock down just the row number.
All right, so what that should do, is bring us the first cell, from cell A1 and as I copy across, I should see the address and city state, and zip.
Now, it's just a simple matter to double click, all right, so Ctrl+C and then Program key+ V.
All right, last thing.
We want to do Format, Column, Autofit Column Width and good to go.
Well, I have to thank Ryan for hitting me with that question at one of our Power Excel Seminars and I want to thank you for stopping by, see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1890.
Unwind Data Using INDEX.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question posted by Ryan at one of my Power Excel Seminars.
He has all of these data going down Column A, these are really fields, like you want to have the name in column C, the street in column D, the city, state, zip in column E.
So, most of the time when I have this problem, well, Sylvia what do you think of it?
That's right, of course, usually I use a macro to do this.
This is the classic example I always use for my macros, but I say, you know, there's a formula that will do this as well.
So we have to count on the fact here, that there are exactly 4 cells for every record.
First record starts in row 1, next record starts a row 5, kind of put that pattern in there and we'll grab the fill handle and drag to get far enough down to the end of the data set.
I guess, I should figure out where the end of the data set was.
We go down to 489, let's take a look if I got far enough down.
No, I did not, so we'll keep dragging some more, it's like both cells that gives it the pattern, there we go, 49, too far.
Alright, so once we have those numbers going down, then across the top of it put the number 0, 1 and 2.
All right, so what we're going to do then, is we're going to start here and do =INDEX($A:$A), now I need to press F4 to make sure that as I copy this formula to the right, it doesn't change to column B and column C. And then it wants to know what row we want, all right.
We want to add two numbers together.
Whatever number is back here in column B, so B2, but I need to lock that down.
Not with both dollar signs, but just with a single dollar sign, before the B. So that's F4, I have to press F4 three times… 2, 3 times, like that, to put “$B2”.
That says: hey, we're always going to point to B, but as we drag it across, it's not going to change from B, but the 2 is allowed to change to 3, 4, 5, 6 as we go down.
And then we're going to add to that the column incrementer, that's up in C1, we’ll press F4, just twice there to lock down just the row number.
All right, so what that should do, is bring us the first cell, from cell A1 and as I copy across, I should see the address and city state, and zip.
Now, it's just a simple matter to double click, all right, so Ctrl+C and then Program key+ V.
All right, last thing.
We want to do Format, Column, Autofit Column Width and good to go.
Well, I have to thank Ryan for hitting me with that question at one of our Power Excel Seminars and I want to thank you for stopping by, see you next time for another netcast from MrExcel.
