Bill Jelen: Hey! I’m Bill Jelen from Misterexcel.com. and I’ve got a cool excel tip for you today.
Mike Gel Girvin: Hey! This is Mike Gel Girvin in Excel is Fun in YouTube and I have a different way to do that.
Mike Gel Girvin: Welcome to another dueling excel pod cast. I’m Mike Girvin from Excel is Fun and Misterexcel, Bill Jelen he longed just a moment from Misterexcel.com.
Hey! Mark sent this question in. He said hey, I have some data and I did a data dumping excel but when it was dumped to the excel the word wrap in the original data set got separated into two cell so apple pie was supposed to be just for record A and one. Down here we have fried fish fillet. So we want to go from this data set to this data set and I don’t have a quick easy clicking solution for this, it’s going to have a bunch of steps. But there will be some cool tricks uh as we go along.
Let’s go over here. I went ahead and put some filled names at the top because we’re eventually going to have a data set where we need to extract some records. I had an extra column because words gather, we need to, for example here we have apple pie, down here we need fried fish fillet and then we need another column for the records we want to keep and eventually extract.
First thing so, let’s highlight this first column and I’m going to hold control and then click and drag and get this column here too because we need to fill in those blanks. Blanks are not going to help us in this data set. To go to blanks quickly, I’m going to hit F5 and then go to special, blanks and then click okay. What does it do? It highlights all the blanks and there are the active cells, so I’ll build my formula which is equals up arrow that is a relative cell reference which says always look one cell above. Control enter to populate all of those formulas into all of those highlighted cells.
Now I’m going to add some color when I’m dealing with a text and formulas like this at least until I get to the end I like to have some color there to tell me, hey that’s a formula. Now words together, I’m going to highlight this whole range here. I’m actually going to hit enter, move the active cell because really what do I want, when I get to this record right here I need apple and pie. Well the formula I’m going to do, we’re going to due an if and we’ll say if this cell is equal to that cell that will trigger the part of the formula, the joints apple and pie. But up here, really all we want is the apple. So watch this, we’re going to say equals if, this cell right here is equal to this cell. Then what do I want? By the way the reason why I’m building my formula from these active cells because I went to the position where I need to do the hard part and if that obviously showed me what the logic is for the test, those two have to be equal.
Alright, so come and that’s a logical test if the value that comes out true, what do we want? We want to say one cell above because remember the formula in just a moment when there’s, when these two are not equal it’ll just slap that value there. That ampersand, that’s the join symbol shift 7 double quote, space, double quite and then another ampersand. What do we want, this right here. Now why do we do it that way? Why didn’t we do apple pie because we always need to look above when they’re—because when you’re down here and we have three fillet right here, oh no I’m sorry. Fried is going to have just fried but when we get down here, the formula will say take fried and get fish. But when we get down here it will say take that one which already has fried fish and join it with that one. So that’s why we construct it, looking one cell above.
Other wise of the value is false we’re just going to take once cell to my left and by the way that will work perfectly because when we don’t need cookies we’ll put there dumplings will be put there, etcetera. Close parenthesis and I’m going to populate all of these cells just like we did over here, control and enter just like that. You could see we get our formula there and it got fried fish fillet and sure enough the bananas, cookies, dumplings were two. I’m actually going to add some green right their. I’m going to control asterisks and highlight the whole thing and add some borders. Now let’s highlight this whole range here and I’m going to do that same trick. I’m going to go down to say right here because I need to figure out this is the record I want, I don’t need fried fish and I definitely don’t need fried.
Well what is the pattern we can recognize? Ah! This is not equal to that. That will work up here too because when we have single values that’s not equal to that, that’s not equal to that. When it gets down to this one, that one, when we say, is that not equal to that, false, and so that we’ll get a false there and we won’t extract that record there. No with this one, no with this, but this one we will. Equals, no if just a true false from it, is that one relative to our reference. Not is less than greater than, not equal to that many relative cells over and one down. Control enter to populate all of the cells. I’m going to add some green, but sure enough true for the apple pie, true, true for all of the single once, false for the A, we got the egg sandwich. So we got all the trues and falses as well where there’s trues we extract the record.
Now watch this, you might say. Oh! I’ll just right click sort or use your sort button on the toolbar in all of our versions but watch what happens, it’s terrible. You never want to sort when you have relative cell reference. Totally, that formula obeyed us. It has relative cell references and sorting does not work, so I’m going to control Z. But filter will filter. You go up to data filter. I’m going to use the keyboard shortcuts, control shift L. Now I can simply come up here, say give me everything, just true and click okay. There it works.
Notice, the blue means we’re filtered. Sorting moved everything. This one just hit them so all of those formulas underneath we don’t see it but there’s still there in perfect order. I’m going to highlight all of these, control C. Notice the answer marching around just the visible cells. Watch this if I click right here and click control V. Why didn’t that work? Because some of the rows are hidden so control Z, highlight and copy. You always want that when you’re pasting come down here and then I’ll paste it down here. Now let’s go look at this. We’re missing—I mean it work fine. We don’t need these columns so I’m going to click there and then I’m going to hold control. Click there, right click delete and just like that we have our data set.
Now, now that all of the records are here, I don’t need, actually this if we want perfect needs to be in the middle, here’s a cool trick you’re going to hold shift and hold to the point on the edge and click and drag and just like a pivot tables in earlier versions that grave our means were moving that and then you’d let go of it. So it’s not healthy—let me do that until I filter this, control shift L and then I could try that trick there. Hold shift, point to the edge, click. I want to see that grey bar, I can drop it and then there we have it maybe I even want to get rid off that fill. Alright I’m going to throw it over to mister excel.
Bill Jelen: Hey Mike that was cool. When I saw this, the first thing I thought of is macros. So I’ll just knock at a quick little macro. The macro is going to work by we select everything in column A that way if it’s more data and I need to assign that a shortcut keys so I’ll click on macros. I called my macro fix them and the options, control A let’s use that. And let’s just see how it works so I have the data selected there, control A and it’s done. Let’s take a look with the macro, click on fix them and edit.
Alright, s here’s what I said. First of all, next row, that’s where I’m going to start to write the output section to. So I sort that out to be two and say for each cell in selections so we have selected these cells here. And the first thing I tried to do, to see is if the cell is greater than nothing, if it is, I need to capture this value here from column A, also column B is equal to offset zero, 1. That zero’s down one column and then column C. so I grab those three values. That’s what happens if there’s something in A. If there’s nothing in A well then what do I do? I say, B is equal to the old B. The B from the last row and a space and the value from this row is column B. Alright so ah that’s my collection process.
Now the other thing I have to do is when I’m on a given row like row three. I need to look and see if the next column A is filled in. If it is filled in, then I want to ride out to the next rows, so remember that starts out to be two and I’m going to resize it to be one row three columns right there for An B and C and then increment, add one to next row.
Now at the very end down here Indian omen. There’s nothing in cell 18 so I have to be careful outside of the loop at the end. I copied these two lines and said we’re going to write it out there and I don’t need to increment next row anymore. And you see that little bit of macro go there, works very, very, very quickly and just to use the simple select how it needs to be, hit control A and you’re done.
So lots of cool techniques that Mike use there. But sometimes a little bit of VBA code makes it very easy. If this a process you’re doing several times a day or even everyday, great to have that macro up and your personal macro workbook and good to go. Hey, thanks for stopping by. I’ll see you next time for another dueling excel pod cast from Excel is Fun and Mister Excel.
Transcription by:
Scribe4you Transcription Services