Mark asks how to fill blanks in with the data from above. Now...if you've watched this podcast, you've seen the trick for this, but Mark's data has something preventing Go To Special Blanks from working! So - another way to go. Episode 1146 shows you how.
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:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Mark, I looked at Mark's question, I knew that I had an answer, and I would be done in 12 seconds.
Mark gets data from a client, and for whatever reason, that client’s GL software does not put the account ID and account description on the ending balance.
And Mark has to sit there and do this, which by the way Mark, isn't working, because look, it's incrementing the account number.
And I said “Well hey, this is going to be a piece of cake.” We're going to select these blank cells here, right?
Ctrl+G for Go To, click Special, click Blanks, click OK, = up arrow, Ctrl+Enter, and we're done!
So I wrote that out in an email to Mark, and I was kind of being snarky, I said “Hey, that's going to take 14 seconds to do.” but I wasn't sure it was going to take 14 seconds, so I said “What the heck, let's try it on Mark’s data.” And as I did this, check it out, Ctrl+G, Special, Blanks, “No cells were found.” What do you mean no cells were found?
They're all blank!
But it turns out they're not blank.
I really don't have a clue what the heck they are, because when I use =ISBLANK, it says they're blank.
But clearly there's something there, I don't know what it is.
And to do this first one, I actually had to go through and delete all those blank rows, mmm, what the heck?
Alright well hey, there's always more than one way to skin a cat, so it would be a painful, Mark has 600 rows of this data to go through and clear all these blanks out, so I said “Let's do this!” Let's select this whole data set here, we'll go to Data, turn on the Auto-filters, and then come here and choose only Ending Balance, alright, now we just get those, alright, now it's going to be a little bit tougher.
First of all, we're going to select these cells, and I'm going to build a formula that's pointing to just above me, I'm in A5 right now, so =A4, Ctrl+Enter.
Oh, I hate that, of course!
Whoever created those is going to save those as text files, choose all and change it back to General.
Text fields, choose that.
Alright, so now here again, =A4, and notice that is the formula because I'm in A5.
Alright, Ctrl+Enter, and it fills everything, and we can turn off the filter, and we filled it in all the way down.
Very, very cool, certainly not the way I expected to do it, probably takes 42 seconds instead of that 14 seconds that this would have taken.
Who creates the data like this?
You know, you wonder what is up with those programmers?
How did they manage to get something in there that makes it look like a blank, but is not really a blank?
So I want to thank Mark for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Mark, I looked at Mark's question, I knew that I had an answer, and I would be done in 12 seconds.
Mark gets data from a client, and for whatever reason, that client’s GL software does not put the account ID and account description on the ending balance.
And Mark has to sit there and do this, which by the way Mark, isn't working, because look, it's incrementing the account number.
And I said “Well hey, this is going to be a piece of cake.” We're going to select these blank cells here, right?
Ctrl+G for Go To, click Special, click Blanks, click OK, = up arrow, Ctrl+Enter, and we're done!
So I wrote that out in an email to Mark, and I was kind of being snarky, I said “Hey, that's going to take 14 seconds to do.” but I wasn't sure it was going to take 14 seconds, so I said “What the heck, let's try it on Mark’s data.” And as I did this, check it out, Ctrl+G, Special, Blanks, “No cells were found.” What do you mean no cells were found?
They're all blank!
But it turns out they're not blank.
I really don't have a clue what the heck they are, because when I use =ISBLANK, it says they're blank.
But clearly there's something there, I don't know what it is.
And to do this first one, I actually had to go through and delete all those blank rows, mmm, what the heck?
Alright well hey, there's always more than one way to skin a cat, so it would be a painful, Mark has 600 rows of this data to go through and clear all these blanks out, so I said “Let's do this!” Let's select this whole data set here, we'll go to Data, turn on the Auto-filters, and then come here and choose only Ending Balance, alright, now we just get those, alright, now it's going to be a little bit tougher.
First of all, we're going to select these cells, and I'm going to build a formula that's pointing to just above me, I'm in A5 right now, so =A4, Ctrl+Enter.
Oh, I hate that, of course!
Whoever created those is going to save those as text files, choose all and change it back to General.
Text fields, choose that.
Alright, so now here again, =A4, and notice that is the formula because I'm in A5.
Alright, Ctrl+Enter, and it fills everything, and we can turn off the filter, and we filled it in all the way down.
Very, very cool, certainly not the way I expected to do it, probably takes 42 seconds instead of that 14 seconds that this would have taken.
Who creates the data like this?
You know, you wonder what is up with those programmers?
How did they manage to get something in there that makes it look like a blank, but is not really a blank?
So I want to thank Mark for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
