Assume a three column database showing Site ID, Customer, Status and Requested Date. On the same site ID, the same customer may have different status on different dates. In such a scenario, one may want to know the farthest/latest requested date and its corresponding status for all unique combinations of Site ID and Customer.

I initially attempted to solve this problem by using a pivot table but the pivot output was incorrect. The pivot was returning the farthest/latest date for all status' of a particular Site ID and Customer. Ideally, it should show only the farthest/latest date and its corresponding status for a particular Site ID and Customer. Therefore, for a particular Site ID and Customer combination, only one row should show up in the final output. Therefore, the Pivot Table solution did not work.

You may download this workbook for a better description of the problem and my workaround.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

**Solution for MS Excel 2010 and higher versions**

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table)

**Solution for all versions of MS Excel**

You may refer my Advanced Filter (along with formula) solution in the workbook.