External data pasted into Excel shows up with 1 row in columns A & C, but multiple rows in B. Today's dueling Excel Episode 1119 shows how to deal with this data.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Mike: Welcome to another Dueling Excel podcast, I'm Mike Girvin from ExcelIsFun, and MrExcel Bill Jelen will be along in just a moment from MrExcel.com.
Hey, Mark sent this question in, he said “Hey, I have some data and I did a data dump in Excel, but when it was dumped into Excel, the WordWrap in the original data set got separated into two cells.
So Apple, Pie, was supposed to be just for record A and 1.” Down here we have Fried, Fish, Fillet.
So, we want to go from this data set to this data set.
Now I don't have a quick, easy, elegant solution for this it's going to have a bunch of steps, but there'll be some cool tricks as we go along.
Let's go over here, I went ahead and put some field names at the top, because we're eventually going to have a data set where we need to extract some records.
I added an extra column called WordsTogether, we need to, for example, here I 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 things though, let's highlight this first column, and I'm going to hold Ctrl, 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 OK, ah, what does it do?
It highlights all the blanks, and there's the active cell.
So I'll build my formula, which is =, up arrow, that is a relative cell reference, which says “Always look 1 cell above”, Ctrl+Enter to populate all of those formulas into all those highlighted cells.
Now I'm going to add some color, when I'm dealing with text and formulas like this, at least ‘till I get to the end, I like to have some color there to tell me “Hey, that's a formula!” Now WordsTogether, I'm going to highlight this whole range here, and 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 build, we're going to do an IF, and we'll say IF this cell is equal to that cell, that'll trigger the part of the formula that joins Apple and Pie, but up here, really all we want is the Apple.
So watch this, we're going to say =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 this active cell, is because I went to the position where I need to do the hard part.
And that obviously showed me what the logic is for the test, those two have to be equal, alright.
So comma, that's the logical test, if the value- if that comes out TRUE, what do we want?
We want to say “one cell above”, because remember the formula in just a moment, when these two are not equal, it'll just slap that value there.
That ampersand, that's the join symbol, Shift+&, “ “&. What do we want, this right here.
Now why do we do it that way, why didn't we do ApplePie?
Because we always need to look above when we’re concatenating, because when you're down here and we have three, Fillet right here, 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'll 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, otherwise if the value is FALSE, we're just going to take one cell to my left.
And by the way, that'll work perfectly because, when we don't need concatenate, Cookies will get put there, Dumplings will be put there, etc.
Close parentheses, and I'm going to populate all these cells, just like we did over here, Ctrl+Enter!
Just like that, you can see we get our formula there, and it got Fried Fish Fillet, and sure enough, the Bananas, Cookies, Dumplings work too.
I'm actually going to add some green right there, I'm going to Ctrl+* 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 that one, we’re going to say “Is that not equal to that?
FALSE!” And so that'll get a FALSE there, and we won't extract that record there, nor this one, nor this one, nor this one.
We will equals, no IF, just TRUE or FALSE for me, is that one, relative cell reference, NOT, is <>, not equal to that many relative cells over and one down, Ctrl+Enter to populate all the cells.
I'm going to add some green, sure enough, TRUE for the Apple Pie, TRUE for all the single ones, FALSE for Egg, we got the Egg Sandwich, so we got all our TRUEs and FALSEs, 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 on the earlier 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 Ctrl+Z, but Filter will!
Filter, you go up to Data, Filter, I'm going to use the keyboard shortcut Ctrl+Shift+L.
Now I can simply come up here, say “Give me just TRUE.” click OK, there, it works!
Notice the blue means were filtered, sorting moved everything, this one just hid them.
So all those formulas underneath, we don't see them, but they're still there in perfect order.
I'M going to highlight all these, Ctrl+C, notice the dancing ants are marching around just the visible cells, watch this.
If I click right here and click Ctrl+V, why didn't it work?
Because some of the rows are hidden, so Ctrl+Z, highlight and copy, you always want to, when you're pasting, come down here, and then paste it down here.
Now let's go look at this, we're missing, I mean it's worked fine, we don't need this column, so I'm going to click there, and then I'm going to hold Ctrl, 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 it perfect, needs to be in the middle, here's a cool trick.
You can hold Shift, and hold to the point on the edge, and click and drag, and just like in Pivot tables, in earlier versions, that grey bar means we're moving it and then you'd let go of it.
Whoops, so it's not going to let me to fit until I un-filter this, Ctrl+Shift+L, and then I could try that trick there.
Hold Shift, point to the edge, click and when I see that grey bar, I can drop it, and then, there we have it, maybe I even want to get rid of that fill.
Alright, I'm going to throw it over to MrExcel!
Bill: Hey Mike, yeah, that was cool!
OK now, when I saw this, the first thing I thought of is macro, so I just knocked out a quick little macro.
The macro is going to work by, we select everything in column A, that way (we know?) if it's more data.
And I need to assign that to a shortcut key, so I click on Macros, I called my macro FixThem, and Options.
Ctrl+A, let's use that, and let's just see how it works.
So I have the data selected there, Ctrl+A, BAM, it's done!
Let's take a look at the macro, click on FixThem and Edit, alright.
So here's what I said, first of all, NextRow, that's where I'm going to start to write the output section to.
So I started out to be 2, and said For Each cell In Selection, so we have selected these cells here.
The first thing I check 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(0, 1), at 0 rows down 1 column over, and in column C. So I grab those three values, that's what happens if there's something in A. If there's nothing at A, well then what do I do?
I say “Hey, well B is equal to the old B, the B from the last row, and a space and the value from this row’s column B.” Alright so, that's my collection process.
Now, the other thing I have to do is, when I'm on a given(?) row like row 3.
I need to look and see if the next column A is filled in, if it is filled in, then I want to write out to the next row.
So remember, that starts out to be 2, I'm going to resize it to be 1 row 3 columns, write up the values for A, B, and C, and then increment, +1 to NextRow.
Now, at the very end down here, for Indian Almond, there's nothing in cell 18, so I have to be careful outside of the loop at the end.
I copied these two lines of code and said “We're going to write it out there.” And I don't need to increment NextRow anymore.
And you see that little bit of macro code there works very, very, very quickly.
And just use the simple, select how wide it needs to be, hit Ctrl+A, and you're done!
So, lots of cool techniques that Mike used there, but sometimes a little bit of VBA code makes it very easy.
If this is a process you're doing several times a day, or even every day, great to have that macro up in your personal macro workbook, and good to go.
Hey, thanks for stopping by, we’ll see you next time for another Dueling Excel podcast from ExcelIsFun and MrExcel!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Mike: Welcome to another Dueling Excel podcast, I'm Mike Girvin from ExcelIsFun, and MrExcel Bill Jelen will be along in just a moment from MrExcel.com.
Hey, Mark sent this question in, he said “Hey, I have some data and I did a data dump in Excel, but when it was dumped into Excel, the WordWrap in the original data set got separated into two cells.
So Apple, Pie, was supposed to be just for record A and 1.” Down here we have Fried, Fish, Fillet.
So, we want to go from this data set to this data set.
Now I don't have a quick, easy, elegant solution for this it's going to have a bunch of steps, but there'll be some cool tricks as we go along.
Let's go over here, I went ahead and put some field names at the top, because we're eventually going to have a data set where we need to extract some records.
I added an extra column called WordsTogether, we need to, for example, here I 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 things though, let's highlight this first column, and I'm going to hold Ctrl, 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 OK, ah, what does it do?
It highlights all the blanks, and there's the active cell.
So I'll build my formula, which is =, up arrow, that is a relative cell reference, which says “Always look 1 cell above”, Ctrl+Enter to populate all of those formulas into all those highlighted cells.
Now I'm going to add some color, when I'm dealing with text and formulas like this, at least ‘till I get to the end, I like to have some color there to tell me “Hey, that's a formula!” Now WordsTogether, I'm going to highlight this whole range here, and 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 build, we're going to do an IF, and we'll say IF this cell is equal to that cell, that'll trigger the part of the formula that joins Apple and Pie, but up here, really all we want is the Apple.
So watch this, we're going to say =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 this active cell, is because I went to the position where I need to do the hard part.
And that obviously showed me what the logic is for the test, those two have to be equal, alright.
So comma, that's the logical test, if the value- if that comes out TRUE, what do we want?
We want to say “one cell above”, because remember the formula in just a moment, when these two are not equal, it'll just slap that value there.
That ampersand, that's the join symbol, Shift+&, “ “&. What do we want, this right here.
Now why do we do it that way, why didn't we do ApplePie?
Because we always need to look above when we’re concatenating, because when you're down here and we have three, Fillet right here, 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'll 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, otherwise if the value is FALSE, we're just going to take one cell to my left.
And by the way, that'll work perfectly because, when we don't need concatenate, Cookies will get put there, Dumplings will be put there, etc.
Close parentheses, and I'm going to populate all these cells, just like we did over here, Ctrl+Enter!
Just like that, you can see we get our formula there, and it got Fried Fish Fillet, and sure enough, the Bananas, Cookies, Dumplings work too.
I'm actually going to add some green right there, I'm going to Ctrl+* 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 that one, we’re going to say “Is that not equal to that?
FALSE!” And so that'll get a FALSE there, and we won't extract that record there, nor this one, nor this one, nor this one.
We will equals, no IF, just TRUE or FALSE for me, is that one, relative cell reference, NOT, is <>, not equal to that many relative cells over and one down, Ctrl+Enter to populate all the cells.
I'm going to add some green, sure enough, TRUE for the Apple Pie, TRUE for all the single ones, FALSE for Egg, we got the Egg Sandwich, so we got all our TRUEs and FALSEs, 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 on the earlier 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 Ctrl+Z, but Filter will!
Filter, you go up to Data, Filter, I'm going to use the keyboard shortcut Ctrl+Shift+L.
Now I can simply come up here, say “Give me just TRUE.” click OK, there, it works!
Notice the blue means were filtered, sorting moved everything, this one just hid them.
So all those formulas underneath, we don't see them, but they're still there in perfect order.
I'M going to highlight all these, Ctrl+C, notice the dancing ants are marching around just the visible cells, watch this.
If I click right here and click Ctrl+V, why didn't it work?
Because some of the rows are hidden, so Ctrl+Z, highlight and copy, you always want to, when you're pasting, come down here, and then paste it down here.
Now let's go look at this, we're missing, I mean it's worked fine, we don't need this column, so I'm going to click there, and then I'm going to hold Ctrl, 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 it perfect, needs to be in the middle, here's a cool trick.
You can hold Shift, and hold to the point on the edge, and click and drag, and just like in Pivot tables, in earlier versions, that grey bar means we're moving it and then you'd let go of it.
Whoops, so it's not going to let me to fit until I un-filter this, Ctrl+Shift+L, and then I could try that trick there.
Hold Shift, point to the edge, click and when I see that grey bar, I can drop it, and then, there we have it, maybe I even want to get rid of that fill.
Alright, I'm going to throw it over to MrExcel!
Bill: Hey Mike, yeah, that was cool!
OK now, when I saw this, the first thing I thought of is macro, so I just knocked out a quick little macro.
The macro is going to work by, we select everything in column A, that way (we know?) if it's more data.
And I need to assign that to a shortcut key, so I click on Macros, I called my macro FixThem, and Options.
Ctrl+A, let's use that, and let's just see how it works.
So I have the data selected there, Ctrl+A, BAM, it's done!
Let's take a look at the macro, click on FixThem and Edit, alright.
So here's what I said, first of all, NextRow, that's where I'm going to start to write the output section to.
So I started out to be 2, and said For Each cell In Selection, so we have selected these cells here.
The first thing I check 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(0, 1), at 0 rows down 1 column over, and in column C. So I grab those three values, that's what happens if there's something in A. If there's nothing at A, well then what do I do?
I say “Hey, well B is equal to the old B, the B from the last row, and a space and the value from this row’s column B.” Alright so, that's my collection process.
Now, the other thing I have to do is, when I'm on a given(?) row like row 3.
I need to look and see if the next column A is filled in, if it is filled in, then I want to write out to the next row.
So remember, that starts out to be 2, I'm going to resize it to be 1 row 3 columns, write up the values for A, B, and C, and then increment, +1 to NextRow.
Now, at the very end down here, for Indian Almond, there's nothing in cell 18, so I have to be careful outside of the loop at the end.
I copied these two lines of code and said “We're going to write it out there.” And I don't need to increment NextRow anymore.
And you see that little bit of macro code there works very, very, very quickly.
And just use the simple, select how wide it needs to be, hit Ctrl+A, and you're done!
So, lots of cool techniques that Mike used there, but sometimes a little bit of VBA code makes it very easy.
If this is a process you're doing several times a day, or even every day, great to have that macro up in your personal macro workbook, and good to go.
Hey, thanks for stopping by, we’ll see you next time for another Dueling Excel podcast from ExcelIsFun and MrExcel!
