A few days ago I solved a problem that I’ve been working on for more than five years: How to create a multi-cell array formula that returns a sorted list of text entries. It’s easy enough to do this with numeric entries, but the text solution has eluded me.

Range A2:A21 is named data. Select C2:C21 and type (or paste) the formula below. Then press Ctrl+Shift+Enter (rather than Enter).

Text entries made in the data range appear in the C2:C21 range in alphabetical order.

Caveat: It doesn’t work for numeric entries, or duplicated text entries.

If you’d like to get rid of the #N/A display, wrap it up in an IF function:

Here’s how to do it using the new IFERROR function (and this may be the best reason yet to upgrade to Excel 2007):

Why did it take me so long to figure this out? I was fixated on an entirely different approach — one that I’m convinced would never work. I hadn’t thought about it for about a year, but then all of a sudden it came to me.

I can’t think of any good reason to actually use this, but I feel pretty good about figuring it out.

Most users know that Excel stores no more than 15-digits of a value. Therefore, if you enter items such as credit card numbers, you must format the cell as Text, or precede the number with an apostrophe.

Try this. Enter the following 18-digit strings into A1:A3. They differ only in the last three digits. Precede each with an apostrophe, or Excel will replace the last three digits with zeros.

123456789012345111
123456789012345222
123456789012345333

Enter this formula in B1:

=COUNTIF(A1:A3,A1)

The formula counts the number of entries that are the same as cell A1. It should return 1, but it actually returns 3. COUNTIF is ignoring the last three characters of the strings. You get the same result if you format the cells as Text, and even this formula returns the wrong answer:

=COUNTIF(A1:A3,”123456789012345111″)

However, this array formula works (enter it with Ctrl+Shift+Enter):

=SUM((A1:A3=A1)*1)

It’s time for another weekend forum at DDoE. Dick is probably kicking back at the lake again, so it’s up to me to get things started.

Post whatever you want here. If you can’t think of anything, how about some Excel humor. Back in the days when I actually kept up my Web site, I had a section called Spreadsheet Jokes.

How about some new additions to VBA Humor? Stuff like this:

• Dim Lotus(1 To 3)
• Dim Mouthful as Byte
• Dim Rope as String
• If IsNull(Warranty) And Void Then BuyaNew1
• Heart = Val(Entine)
• Lonely = Left(Out, 1)
• Map = Rand(McNally)

And, with Excel 2007’s expanded grid, the Famous Cells and Ranges section should have lots of possibilities. Some old ones:

• A1 – The steak sauce cell
• IM21 – The legal drinking age cell
• K9 – The dog cell
• AK47 – The assault weapon cell
• IV2 – The second intravenous solution cell
• B9 – The malignant cell
• HI5 – The alternate handshake cell
• AH:HA – The discovery range

Have at it.

Does anyone use non-standard UserForm controls? By non-standard, I mean those that normally don’t appear in the VB Toolbox.

I generally avoid using such controls because I’m afraid that they won’t work reliably for all users. Today I was playing around with the Microsoft Date and Time Picker 6.0. I don’t even know where it came from. Is it installed with Windows? In any case, I had a few problems. Specifically, occasional “object could not be found” errors. Restarting Excel solved the problem. I was using Excel 2007 beta, so that could explain it.

I’m interested to hear about your success (or failure) using 3rd-party ActiveX controls on UserForms.

I’m posting this in case Dick forgot about the new weekend tradition here at DDoE. This is where you can post anything you like.

This was a good week for posts. Lots of interesting stuff. One of these days I’ll actually read all of the VB.NET stuff XL-Dennis posted.

Does anyone need to get a new battery from Dell to replace their flammable one?

Today was my deadline for turning in the final chapters for Excel 2007 Bible. All that remains is the Preface, and that should take me about 30 minutes. But the book is certainly not finished. It will be reviewed by the project editor, a copy editor, and a technical editor. When the next Office 2007 beta is released, I’ll have one more chance to make changes, additions, and make sure the screen shots are accurate.

I was surprised to see that the book is already listed on Amazon, and it has a sales rank of 172,384. How can that be?

The Amazon listing (and also the book cover mock-up) indicates that the book’s CD will include a trial version of my Power Utility Pak add-in. Unfortunately, that’s not the case. PUP will require some major changes before it works well with Excel 2007. I plan on making a special version that will work only with Excel 2007, but I won’t have time to do that until the books are finished. Next up is Excel 2007 VBA Programming For Dummies. That will be followed by Excel 2007 Power Programming With VBA, and then the fun one: Excel 2007 Charts.

After working with Excel 2007, I’m starting to like it quite a bit. A few weeks ago, I turned the corner and realized that I much prefer Excel 2007 over Excel 2003. It’s really just a matter of getting used to the new interface. Now, I have a hard time remembering where the commands are in Excel 2003. A key difference is appearance. The new fonts and graphics are just much nicer to work with. When I open a file in Excel 2003, it looks old and clunky to me. And the charts look atrocious compared to the new charts.

Excel 2007 certainly isn’t perfect. Some of the deficiencies, I think, will be fixed in the final version. But we’ll just have to live with the other problems — the main one being the inability for the end user to customize the user interface. A major upgrade like this involves trade-offs. You give up some things and get other things in return. All things considered, the scale definitely tips in favor of Excel 2007.

(also posted at J-Walk Blog)

I’ve been playing around with Excel 2007’s conditional formatting. Here’s a 150×150 range of cells that uses one of the new color scale conditional formats. It’s a plot of SIN(x) x COS(y) for x and y values ranging from 1-4.

I used ;;; number formatting to hide the numbers in the cells, so all you see is a very cool gradient contour “chart.” This is a small version. Click the image to see a larger one (captured while the screen was zoomed to 10%).

The amazing thing is how fast it is. If I change the formula or the increments, the color refresh is almost instantaneous.