How to Sort Multiple Columns in Excel 2007

When working with large datasets in Excel 2007, it's often necessary to sort multiple columns to gain insights into your data. In this article, we'll explore how to achieve this using various techniques and formulas.

In our example, we have a dataset that tracks client information, including short-term and long-term services. The goal is to create a flat file with all services sorted by client ID, then the service start date, along with the short-term episode number and all service episode number.

To accomplish this, we'll use Excel's built-in sorting feature and some clever formulas. But before we dive into the solution, let's understand the requirements:

* Long-term data is refreshed when the Excel file is updated.

* Current short-term data is refreshed every 6 weeks or so.

* Historic short-term data is refreshed 2-3 times per year.

* We need to create pivot tables detailing the length of time it takes to start a long-term service after the short-term service.

To get started, we'll use the following formulas:

* `=IFERROR(formula1, formula2)` This formula ensures that when values run out from List1, it returns errors and then moves to formula2. Formula2 is identical to formula1 except that it returns values from List2.

* `COUNTIF('Unique distinct list',)` This function counts values based on a condition, in this case, counting values in cells above.

Now, let's create the flat file:

Step 1: Sort by Client ID

Select the entire dataset and go to the 'Data' tab. Click on 'Sort & Filter' > 'Sort'. In the 'Sort by' dropdown menu, select 'Client ID'.

Step 2: Sort within each client by Service Start Date

Still in the 'Sort & Filter' dialog box, click on the 'Options' button and then select 'Advanced'. Under 'Sort order', choose 'Ascending'. This will sort within each client by service start date.

Step 3: Add Short-term Episode Number and All Service Episode Number

Create two new columns to capture the short-term episode number and all service episode number. Use formulas like `=IFERROR(COUNTIF(A:A, A2), COUNTIF(B:B, B2))` to count the occurrences of each client's services.

Step 4: Sort the entire dataset by Client ID, then Service Start Date

Repeat steps 1 and 2 to sort the entire dataset by client ID and service start date.

By following these steps, we can efficiently sort multiple columns in Excel 2007 and create a flat file that meets our requirements. This technique is particularly useful when working with large datasets and trying to extract specific insights from your data.

How to sort multiple columns in excel 2007