Dynamic Sorting in Excel 2007/2010 – How to Enable

microsoft excelsortingworksheet-function

I have a task list spreadsheet that I maintain. Each task is given a ranking based on number of days until due date, weighted by high, low, or medium priority. The ranking is automatically calculated, and the resulting list manually sorted to yield the current list of to-do's in priority order.

In a perfect world, I'd like to set up a second tab in my worksheet that would display the data from my first tab, but sort it for me on the fly. For instance, if I entered a new task that was due today with a high priority, I could flip to sheet 2 and it would already be automagically sorted to the top of the list.

Sheet One (as entered; number is calculated weight)

Task 01, 06/20/2013, Low,  0009
Task 02, 06/20/2013, High, 0999
Task 03, 06/30/2013, Med,  0089
Task 04, 06/19/2013, High, 1000  <-- new entry

Sheet Two (automatically sorted, descending order on calculated weight)

Task 04, 06/19/2013, High, 1000 
Task 02, 06/20/2013, High, 0999
Task 03, 06/30/2013, Med,  0089
Task 01, 06/20/2013, Low,  0009

We're transitioning from 2007 to 2010 in our organization, so I want to make sure that any soutions work in both versions. Any ideas?

Best Answer

Excel does this sort of thing easily. You shouldn't even need a second sheet (although you could certainly use one).

Convert your list of tasks to a Table Insert>Tables>Table.
In your Calculated Weight Column, use the drop down arrow and select Sort Largest to Smallest.
Each time you add a row to your table, just re-sort and you'll always have the highest priority first.

Here's what the Table and associated Pivot would look like. You could place them on the same or separate sheets, to your preference.

Table and Pivot

As an alternative, you could have this table feed a Pivot Table which would always be sorted in the same manner, but you'll still have to refresh the Pivot Table whenever you update the original data Table (whether manually or automatically based upon a time period or workbook open). This functionality is available in both 2007 and 2010.

Step 1

[Updated]

In the "Numbers" column, highlight the range, then split the text at the hyphen: do...

Data > Text to Columns > Delimited > Next > Other: - > Finish

Notice you need a hyphen (-) in the Other: textbox. And make sure that the adjacent column (to the right) is empty before you do this, so that you don't overwrite important data.

You could also use this function to do extract the number from column A:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2))

Step 2

Now, what we're going to do in the next step, as you can tell from the screenshot above, is append zeroes to the beginning of each of the numbers which have fewer digits than the largest number. This will allow you to sort these numbers in the way you desire.

But first, we need to do a bit of fact-finding. If you can tell easily what the largest number is, then just count the digits in that largest number — this will be the number of zeroes you want to use in our next function. There is another way to determine the longest number, without having to count the digits of the largest number manually.

[UPDATE] You can use the following function (though so far we don't quite know why) to determine the longest number:

=MAX(INDEX(LEN(C2:C14),,1))

Alternatively, you could simply type the following formula into a cell (you see this in the image above as the cell highlighted orange), but instead of simply hitting your ENTER key to set the cell, hit the hotkey CTRL-SHIFT-ENTER. This will change the nature of the function, turning it into an array formula, without having to toy with functions such as INDEX().

=MAX(LEN(C2:C14))

(Make sure that the range is accurate for the specific spreadsheet you are using.)

After you hit CTRL-SHIFT-ENTER, the content of the cell will change to this, but typing this manually will do nothing:

{=MAX(LEN(C2:C14))}

However you want to do it is fine. Just determine how many digits make up the largest number in your list: "1" of course has 1 digit, "10" has 2 digits, "100" has 3 digits, and so on.

Step 3

Finally, in the "Expand" column, this function will convert the numbers from the "Numbers" column into text with the number of preceding zeroes you determined you should use in Step 2.

=TEXT(C2,"000")

Make certain that you put quotations marks around the zeroes.

If the largest number has 8 digits, then your function will look like this:

=TEXT(C2,"00000000")

Best Answer

Related Solutions

Excel – Alphanumeric sorting in Excel

Step 1

Step 2

Step 3

Related Question