Sort data where each record occupies three rows.
Erik asks how do deal with data where each record takes up three rows. Episode #1176 shows you how.
Erik asks how do deal with data where each record takes up three rows. Episode #1176 shows you how.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey alright, welcome back to the MrExcel netcast, I'm Bill Jelen. Now here's a question sent in by Eric, and I've seen this question before, Eric has a spreadsheet where each record takes up three rows. So you know, there's some data up here in the grey, and then a bunch of numbers in the yellow, and then some other numbers down here in the white. And so how do we deal with this? Now the other thing that is going to screw up Eric's data is he has two headings up here, so I actually want to insert a row so that way we only have one row of headings.
We're going to insert a couple of columns, and I'm going to do something called sequence.
And the way that sequence is going to work, is put 1, 2, and 3, and then I'm just going to use an = to go up and grab the number from 3 rows above. That's going to do this nice little 123123 all the way down, we'll copy that, Ctrl+C, and then Alt E S V for Edit Paste Special Values, and then we're going to use SortKey. Now, here's how SortKey is going to work, it's going to say =IF, IF this number to my right is =1, then I want the name from over here in D4, otherwise, I want the value from just above me. Alright, and watch what happens when we double-click to shoot that down, you'll see that the name, that's the sort field, comes over to column B for every one of those records.
Now again, when it convert those to values, this time I'm going to right-click, drag right, drag left, let go, copy here as values only, several different ways to solve that problem.
Now, we'll click Data, A-Z, and everything gets sorted by the name, but all of the data came together. We can now delete these extra rows out here, delete, and then delete the extra row, those were columns out there, and then the extra row.
There you go, hey Eric, thanks for sending the question in, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey alright, welcome back to the MrExcel netcast, I'm Bill Jelen. Now here's a question sent in by Eric, and I've seen this question before, Eric has a spreadsheet where each record takes up three rows. So you know, there's some data up here in the grey, and then a bunch of numbers in the yellow, and then some other numbers down here in the white. And so how do we deal with this? Now the other thing that is going to screw up Eric's data is he has two headings up here, so I actually want to insert a row so that way we only have one row of headings.
We're going to insert a couple of columns, and I'm going to do something called sequence.
And the way that sequence is going to work, is put 1, 2, and 3, and then I'm just going to use an = to go up and grab the number from 3 rows above. That's going to do this nice little 123123 all the way down, we'll copy that, Ctrl+C, and then Alt E S V for Edit Paste Special Values, and then we're going to use SortKey. Now, here's how SortKey is going to work, it's going to say =IF, IF this number to my right is =1, then I want the name from over here in D4, otherwise, I want the value from just above me. Alright, and watch what happens when we double-click to shoot that down, you'll see that the name, that's the sort field, comes over to column B for every one of those records.
Now again, when it convert those to values, this time I'm going to right-click, drag right, drag left, let go, copy here as values only, several different ways to solve that problem.
Now, we'll click Data, A-Z, and everything gets sorted by the name, but all of the data came together. We can now delete these extra rows out here, delete, and then delete the extra row, those were columns out there, and then the extra row.
There you go, hey Eric, thanks for sending the question in, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
