The ExcelHelpDesk team received the following request for help….
I have a workbook that has two sheets: sheet1 contains two people (ID = 1 or 2), and each person has multiple records of DATE. Could you kindly inform me how to get the “earliest date” for each ID, so the table in Sheet1 will become the table in Sheet2? By the way, I am not familiar with EXCEL MACRO although I am a strong SAS SQL programer.
Where can I find the EXCEL SQL (free introduction material) for beginner, please?
The solution to this problem can be approached in a number of ways. In this post we will describe and method that uses built-in Excel functions to derive the result.
VLOOKUP (Excel Function)
Select all values in Column A and B, then Sort the selection by Column A ascending and Column B oldest to newest. The Sort option dialog would look like
and the data would like the following after completing the Sort
Now in Sheet2 we need to use an Excel function to lookup and find the first value in the list of values on Sheet1. We will use the VLOOKUP function to search the list on Sheet1 and when the first match is found return that value to the list on Sheet2.
In Sheet2 select cell B2, then select the Formulas Tab (Excel 2007) select Insert Function and choose VLOOKUP. Now specify the following values in the various options for the function.
The Col_index_num option indicates which field to return from the original list. The Range_lookup must be specified as FALSE, so that the first result is matched when the lookup is performed.
When the function is completed for the first cell B2 and the result is returned, drag that function to the cell B3 and the VLOOKUP will perform the same lookup for the second record and return the earliest date from the Sheet1 list. When dragging the function to the next cell make sure that the Table_array parameter is set to Sheet1!A1:B8
Finally we have the result showing the earliest dates for each of the ID’s in Sheet1
Another option to acheive the same result would be a VBA macro to search the list and return the earliest date.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.