Hey welcome back to the Mr. Excel 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 will be done in 12 seconds. Mark gets data from a client and for whatever reason, the 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. Man 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, equal up error, Ctrl Enter and we’re done. So I wrote that down in the email to Mark and I was kind of being snarky yesterday, that’s going to take 14 seconds to do but I wasn’t sure it’s going to take 14 seconds so what the heck, let’s try it on Mark’s data and as I did, check it out, Ctrl G, special blanks, new cells were found. What do you mean new 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 equal is blank, it says they're blank. But clearly there is something there.
I don’t know what it is and to do this, to do this first one, I actually had to go through and delete all those blank rows. What the heck? Well hey there’s always more than one way to skin a cat so it would be painful, Mark has 600 rows of this data go through and clear all these blanks out so I said, let’s do this. Let’s select this whole data set here, go to data, turn on the auto filters, and then come here and choose ending balance. Now we just get those. All right 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 with the point just above me. I'm in A5 right now so equal A4, Ctrl + Enter, oh I hate that. Of course, whatever grid this is going to save those as text files, choose all and change it back to general. Text fields, text fields, choose that, all right so now here again, equal A4 and notice that is the formula because I'm in a 5, all right, Ctrl + Enter and it fills everything in. We’re going to just 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 the 14 seconds that this would have taken.
Who creates the data like this? You know you wonder what is up with those programmers, how do 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 Mr. Excel.
Transcription by:
Scribe4you Transcription Services