Excel formula to sort data based on unique individuals?

Question

I have approx 29,000 rows of data and three columns: a unique ID, an admission date and a departure date

I need to sort the data so that:

1) The admission dates are in order from newest to oldest for each individual ID (i.e. the admin dates associated ONLY with the ID 455 are ordered from newest to oldest and then start again and do the same thing with ID 677 and 899 etc.); and

2) The departure date associated with each admin date stay locked together.

Does anyone know if this is possible, and if so, how I might go about achieving it?

Thanks so much advance!

Answer 1

Using the built-in menu features in Excel (2016 for example):

• Select all of the data in all 3 columns
• Choose the 'Sort' function under the 'Data' tab.

• Check the "My data has headers" box
• Under 'Column', select "Sort by: ID" and verify 'Order' is "Smallest to Largest"

• Press the 'Add Level' button
• Under 'Column', select "Then by: Admission Date" and verify 'Order' is "Oldest to Newest" "Newest to Oldest"
• Press the 'OK' button

That should do it.
Older/newer versions of Excel also have this 'Sort' functionality although the interface & menus may be slightly different.

To accomplish this with a formula (as the question mentions), some complex usage of 'VLOOKUP()' could work...

Answer 2

If you want a dynamic approach, you can build a Pivot Table. Drag ID, Admission Date and Departure date into the rows area. Use a tabular layout and don't show subtotals (these options are on the Design ribbon of the Pivot Table Tools).

Pivot tables automatically sort data ascending. Here is a quick example.

When new data is added to the source table, you just need to refresh the pivot table.