Daily Dose of Excel

El Says:
“I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day.”

This is where the Subtotals feature is quite useful.
It can add a variety of footers to appear at breaks in the group. You get outlining too.
Select your range, then from the Data menu, choose Subtotals…
At each change in StartDate, Use function Sum, Add subtotal to Duration.

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).

=INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0))

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:

=IF(ISNA(INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0))),"",INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0)))

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

=IFERROR(INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0)),"")

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.

Jim T. sends this along, and I’m not sure what’s causing this behavior. Maybe you know and comment on it.

Col. D and Col. E are identical except that Col. E’s formula has range references that include the sheet name.

If you sort Col. D without sorting any other data on the sheet, you get the expected result (at least the result that I expect). That is, nothing happens. The formulas move to a different location and their range references change relatively. The formula that references A2, moves to D11 and then references A11. It looks like nothing happened.

Why then when I sort Col. E, does the same thing not happen? It sorts based on the values, but the formulas don’t change. Or maybe they do change - however you want to look at it.

In Sort On More Than Three Columns, I described how to short your data  in two steps.  Mark’s tip for sorting on more than three columns uses a helper column.

Using another column, you can concantenate the columns you want to sort and sort on that column.  This example sorts on four columns by using a fifth column to string together the other four.

There is a problem with this method.  In the screen shot below, Dick’s week 5 entry is sorted above John’s.  The ‘Number’ column is coverted to text in the concatenation process and 18 is less than 5 when their both considered to be text.  Text is sorted by comparing characters from left to right.

So be careful if you use this method.  Thanks for the tip, Mark.

From Jan, a workbook that doesn’t sort quite right. The four numbers in column A are sorted ascending with a header row

This often happens with imported or copied and pasted data. It all looks normal because the cells are right justified. If you remove the justification, you would see that only the 4 is really a number and the other numbers are really text that look like numbers. Excel sorts text and numbers separately.

This can be fixed by converting all the “text” values to actual numbers, as described here. Put a 1 in an unused cell, copy it and Paste Special > Mulitply over the range. That will coerce all the entries in the range to numbers and it will sort it correctly.