Daily Dose of Excel

In Part 1 we ended up with Column D, a sorted list. One criticism was the many times we were counting the numbers in a column. We should improve it and only count once. Via Insert/Name/Define define Count_BB to =COUNT(Sheet1!$B:$B), and then select Columns C:D, and “Replace All” COUNT(B:B) with Count_BB. Column D should look something like this:

 
In Cell E1 we simply move D1 over. In E2 we check to see if D2 is not equal to the cell above it, or in other words D2 starts a new run of names. If it is equal (FALSE condition) we put empty text. Otherwise, we put D2. Fill down from E2 as far down as columns B, C, and D are filled. Column E now looks like this. We have removed the duplicates.

 
In F1 we check to see if there is anything of length in E1. If there is, put the row number, otherwise put empty text. As above, define Count_FF as =COUNT(Sheet1!$F$F). After filling down, Column F looks like this:

 
In G1 we again test the row number, but this time it’s against the count of numbers in F:F. If the ROW() is less than or equal to Count_FF, put the numbers from F:F there is ROW() order. Fill down as before. Column G looks like this:

 
Last Step. In H1 we again compare the row number to the count of numbers in F:F. If ROW() is less than or equal to the Count_FF, then index E:E (could also be D:D) the number of rows shown in G:G. Fill down as before. Column H looks like this:

 
The list that started in A:A is now sorted, de-duplicated, and collapsed to unique values, ready in H:H for whatever you might need as you write your fantasy novel. All you need to remember is to fill B2:Hn down well beyond any possible extent of A:A. This can be extended to handle several field records by indexing the appropriate columns at the two appropriate points.

…mrt

You can download self_sorting_names.zip

I haven’t posted in a good while. I’ve done over half the Euler problems, but haven’t had the time to research the high-numbered ones considering I might even understand them. Now that I’ve retired from my second career, and my third is only part-time, maybe I’ll get back to it. Today is something perhaps more useful…to have a list of names, specifications, part numbers, etc and have them self-sort, de-duplicate, and collapse without ever having to go to the Sort Menu. Add a member to the bottom, and it flows through. Replace or paste over the data, and Excel turns the crank. Unique values appear, and no VBA is involved. This was a monthly chore in Job #2 for sometimes as many as 8000 email addresses.

Assume in Column A are 1000 (fantasy) names, in random order. These names were generated by using this website four times. Rice University and Dr. Chris Brown have provided name generation tools for many spoken languages. Your Column A might look something like this:

The formula in B1 tests if there in anything of length in A1. I’ve found that the LEN() function always returns a value that agrees with my ol’ Mark 1 Mod 0 eyeballs, and empty text doesn’t mislead me. If there is something of length in A1, then COUNTIF() everything in A:A that is less than A1. This uses Excel’s lexicographic algorithms, and that opinion may differ from what you want, but I’ve never had a problem with it. Add to that count using mixed references the COUNTIF() of everything between $A$1 and $A1 inclusive that equals $A1. When we fill down this will become COUNTIF($A$1:$A2,”=”&A2) in A2. Otherwise, if there is nothing of length, place empty text (””-double double quotes). Fill down well below the extent of values in A:A.

Column B now looks something like this:

The formula in C1 tests if the row number is less than or equal to the count of the numbers in Column B. If it is, put the the smallest number there from Column B:B in ROW() order, otherwise put empty text. After filling down as far as in Column B, Column C is just 1 through the extent of your data in Column A. The use of SMALL() is what accommodates blank cells in A:A.

The formula in D1 tests the ROW() against the COUNT() again, and if ROW() is less than or equal to the COUNT(), specify zero as the third argument and find the exact MATCH() of the number in Column C:C within Column B:B, and then INDEX down Column A:A that far and return that result. Otherwise, put empty text. Fill down as far as in Columns B and C. After filling down, the list is sorted, and might look something like this:

Next post we’ll de-dupe the list and then collapse it to unique entries.

…mrt

If you want your Excel app to default to a specific directory when opening or saving files, see Changing the Current Directory. Be sure to read the comments.

I have a slightly different situation. I want my app to open to a specific directory that will give me easy access to sub directories. However, if the current directory is already a sub directory of my base directory, then it’s pretty likely to be in the one I want and I don’t want to change it. For example, my base directory is

If I’m in S:\Flash\Payroll\2010\0824\", then that’s probably the directory I want and don’t want to change. However, if I’m in S:\Flash\Accounting\Reporting\, then I want to change to my base directory.

Pretty simple. If it’s at the base or a sub directory, don’t do anything. If it’s anything else, go to the base directory. Upon further reflection, though, it seems that a more general purpose procedure is in order.

Now I can call it from multiple locations and pass in the base directory. If the left x characters is the base directory, don’t do anything. Otherwise, change the drive to the first two characters of sBase (e.g. “S:”) and change the directory to the base. It won’t work with UNC paths. Any other problems with it?

Jake wants to know, given an active date and an inactive date, how to count the customers that were active in a certain time period.

The ones we want are highlighted in yellow. The formula is

It’s easier to figure out who is not active during that date range and subtract it from the total. The formula starts by counting everyone using the COUNT function. The SUMPRODUCT function is then subtracted from that. It gives the total of all the customers who became active after our End date plus the customers who became inactive before our Start date.

Note that this formula excludes the actual Start and End date. Customer 17 isn’t included because we’re really looking at 7/16/2010 to 7/24/2010. If you want the formula to be inclusive, simply change the <= and >= to < and >, respectively.

If you don’t like SUMPRODUCT, you can get there with COUNTIF

Sometimes it’s easier to turn the problem around and figure out who’s excluded.

A couple of weeks ago, I posted some code to print labels on a Dymo LabelWriter 450. I wanted to post the finished code because it has a few more tricks in it.

The procedure is now a function that returns a Boolean because I use the error logging scheme described in PED. But if you ignore all that stuff, there are a couple of changes worth noting.

That would prevent errors if someone moves or renames the label file. Always build in some potential errors for job security (just kidding, don’t do that). Someday when I have some more time, I’ll tighten up the code further. But for now, it will have to do.

Last week I was creating file folder labels with my new Dymo LaserWriter 450. The information on the folder label is serial number, part number, company name, and purchase order. Normally, this works great. However, I ran into one on Friday with a 30 character part name and a 29 character company name. With shrink-to-fit, the font came out at about 4 points, surprisingly readable, but too small for these old eyes.

When we did this manually on our old label maker, we would abbreviate the company name and I wanted to replicate that in my code. It’s easy to do for humans. Less so for VBA. I started by removing all of the lower-case vowels.

General Electric -> Gnrl Elctrc

Not bad. Not, of course, how I would abbreviate it manually (GE), but not bad. By only removing lower case, I preserved the first letter and any initials that would seem to be key to deciphering the name. I still had names that were too long. I was trying to get them to 15 characters or less, and this method only got about half of them to less than that. Next, I removed Inc., LTD, Corporation, Corp and all that extraneous stuff that’s not critical to identifying the name. You have to be careful with case sensitivity or you can end up with

Principle Technologies, Inc. -> Prcpl Tchnlgs

because of the embedded “inc” in Principle.

Finally, I removed punctuation and got

If you implement something like this, you want to make sure that the longer terms come before the shorter ones. If you remove Corp before you remove Corporation, you’ll end up with “oration”, which doesn’t make any sense.

In the end, and a little to my surprise, this didn’t work very well. Looking at the transformed names, I was able to identify about 80% of them. That’s not very good, in my opinion. Then my co-worker suggested I use two lines on the file folder label. I smacked my forehead and did just that, and of course, it works beautifully. It was a fun exercise nonetheless.

A favorite peeve of mine is code with “bushy trees.” I first saw this phrase in Kernighan and Plauger’s Elements of Programming Style.

Recently, I saw some code that checked if a RefEdit control referred to a single cell that contained a non negative integer. I cleaned up the formatting some since the original indentation style might be best described as “random tabs.” But, code formatting is not the subject of this post.

The IFs are fairly easy to understand since they essentially follow how most people would think to validate the string parameter. The tricky part comes in ensuring that one has the correct Else clause at the correct indentation level. In this case, that too might not be too difficult since the Else clauses are fairly trivial, each consisting of a single boolean assignment. But, imagine how much more difficult the task would be if there were further If clauses or loop structures in some of the Else clauses!

Code Sample 1

As a first pass, one could remove all the boolean assignments by first setting the function value to false and not to true as in Code Sample 1. Then only if we have an acceptable value do we return a True value.

Code Sample 2

However, this still doesn’t help with all the nested If and End If clauses.

So, how does one clean up this deeply nested code? How about if we reverse the tests? Instead of testing if the range is not nothing, test if it is nothing. Instead of testing if the range contains 1 cell, test if it contains more than 1 cell. And, so on. The result as shown in Code Sample 3 is a single If statement (with multiple ElseIf clauses) that is ‘flat’ — with no confusing nesting!

Code Sample 3

The code above uses a very powerful concept — that of a ‘null clause.’

In most cases, when we have a If…Then, we have some statement in the True branch of the If statement. It might be a series of assignments or it might be another If or a loop of some sort but there is something in the True branch. For example, in Code Sample 1 and Code Sample 2 above, there are two assignments.

However, in Code Sample 3, each Then is followed not by a statement but by the ElseIf clause. This results in a null statement (or empty block) in the True branch. This is perfectly legal in every programming language I’ve used and in this case it serves a very powerful role in simplifying the code.

For the sake of completeness, we will look at one more way of coding the above. In this particular scenario since there is no further processing after the string is validated, one could use use a series of Ifs that simply go to an exit point for bad data. But, this would not work for scenarios in which we wanted to do additional processing after the string of Ifs.

Code Sample 4

Hi folks,

Many of the regulars here probably know the Name Manager utility, which Charles Williams and I created and give away for free on our websites.

Rumour has it this is one of best tools ever built for the Excel developer. I won’t argue with that!

Anyway, as I was looking at my web stats today I discovered a nice feat: We’ve just passed the 100,000 download count on the tool (this excludes the downloads from Charles’ site, so we can safely assume the true number is at least 50% more than that). Time for a celebration:

Hurray!

Regards,

Jan Karel Pieterse
www.jkp-ads.com