No Links were listed yet. Go ahead and share!
The Mr. Excel podcast is brought to you by Easy Excel.
Hey, welcome back to the Mr. Excel 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 there’s some data up here in the gray and a bunch of numbers in the yellow and then some other numbers down here in the white so how do we deal with this. 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 we only have one row of headings and 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 in 1, 2, and 3 and then I’m just going to use an equal sign to go up and grab the number from 3 rows above. That’s going to do this nice little 123, 123 all the way down.
We’ll copy that, Ctrl C and then Alt ESV, for edit paste special values and then we’re going to use sort key. Now, here’s how sort key is going to work. It’s going to say = if this number to my right is equal to 1 then I want the name from over here in D4 otherwise, I want the value from just above me. 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, if we want to convert those to values, this time, I’m going to right click, drag right, drag left, let go, copy here as values only so everybody needs 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.
Eric thanks for sending that question in. Thanks to you for stopping by. We’ll see you next time for another Netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services