Digithoughts

The purpose of this post is to teach how to create auto-sorting for data within Excel, without having to manually click Sort. This how-to is especially targeted for those who need to sort Omniture's ExcelClient data blocks.

Problem: The data is sorted by the total date range (4 weeks). We need the data sorted by the most recent week.

Solution: Create auto-sorting, following the steps below to always sort the data by the most recent week.

Directions on how to create auto-sorting in Excel

Step 1: Identify the data to be sorted

• Let's say you have a table with two columns that you want to sort by the second column. This is similar to sorting by the most recent week.
• The following table of two columns (B and C) are refreshed every week.

Step 2: Create the Sorting Logic data block

• Sorting requires three additional columns that make up the "Sorting Logic data block," which must be next to each other. However, the Sorting Logic data block as a whole can be located far away from the data. For this example, we will be using columns E through G.
• The first column to create is the middle (column F), which is just an increasing sequence of numbers. NOTE: There needs to be at least the same increasing sequence of numbers for all the unsorted data.

Step 3: Creating the sort logic key

• The second column to create is the list of values by which to sort the table, called the "key." We will be using column C of the data as our "key" values.
•  Important Notes about the "key" values:
• If the "key" values are not unique, the sorting will not work correctly.
• If the "key" are all integers (whole numbers), we can make them unique by adding to each a unique value less than 1.
• To do this, use the sequence of numbers (in column F). Divide the increasing number by 1,000 and add it to the "key" value.

Step 4: Creating the Row Location of Rankings

• The last column to create is the "row location of rankings," located on the far right (column G in the example). This column shows the row location of each ranked data point.
• Find the Nth-largest value in the key (column E) by using the LARGE function. The function reads "Of this array of data, what is the Nth-largest value (according to column F)?"
• Find what row this value is on by using a VLOOKUP to find where the key is ranked (using column F).

Step 5: Applying Sorting Logic data block to sort the data.

• Index the array, using the "row location of rankings" number (column G) created in Step 4. The index function will find the data that's N rows down.
• The example below has the data sorted in column J. The data uses the original numbers (in column C). After the sorting logic is applied, the index function will return the sorted data, based on highest value to lowest value.

Example

• Below is an example for "Page view share for the top 10 pages in the Sample section." The Sorting Logic has been added on the side (similar to the above steps)

Example:

• Below is the sorted example data block, that indexes the Sorting Logic to create the auto-sorting from largest to smallest values.
• The Index formula is referencing a different area on the spreadsheet that has all the Sorting Logic for all ExcelClient data blocks that need sorted.

Logan Gordon Web Analyst

Read more from the Analytics category. If you would like to leave a comment, click here: Comment or stay up to date with this post via RSS, or you can Trackback from your site.