Find first and last order date for ID (same id might appear multiple times) in Excel

Question

I have a list of orders with the following columuns: Buyer ID, Order Date, Order Value

I need to add one column that lists the FIRST order date, and one that lists the LAST order date. In case of only 1 order, the date would be the same in both columns.

EG - this is what I have

Buyer ID   Order Date
1          1/31/2016
2          2/27/2016   
1          5/31/2016

This is what I would like

Buyer ID   Order Date   Last Order   First Order
1          1/31/2016    5/31/2016    1/31/2016
2          2/27/2016    2/27/2016    2/27/2016
1          5/31/2016    5/31/2016    1/31/2016

I have over 1000 buyer IDs, not all of them have multiple orders

Answer 1

Let's assume your "Buyer ID" values are in cells A2:A4, and your "Order Date" values are in cells B2:B4. To get the "Last" value in cell C2 (the topmost non-header cell in your "Last Order" column), type or paste the following formula, then press Ctrl+Shift+Enter to enter it:

=MAX(IF($A$2:$A$4 = A2, $B$2:$B$4))

Then, to get the "First" value in cell D2 (the topmost non-header cell in your "First Order" column), type or paste the following formula, and again press Ctrl+Shift+Enter to enter it:

=MIN(IF($A$2:$A$4 = A2, $B$2:$B$4))

Then, simply formula-copy the cells C2:D2 down to as many rows as you have data.

Please note that it is important to enter the formulas using Ctrl+Shift+Enter instead of just pressing the Enter key, as this will create an array formula. Otherwise, in each cell you would get the MAX or MIN values for the entire range A2:A4, without taking the "Buyer ID" criterion into consideration.