Make a Report depending upon the Greatest Value

**habibian88** · 07-31-2012, 12:48 PM

I HAVE AN EXCEL WORKSHEET WHICH IS SOMEWHAT LIKE THE FOLLOWING;

Item ID Date Quantity
123 01-Aug 12
123 03-Aug 4
123 03-Aug 343
123 03-Mar 4
123 05-Aug 454
124 07-Aug 5
124 08-Aug 65
125 01-Aug 3
125 05-Aug 4
125 09-Aug 2

I WANT NOW TO CREATE A REPORT IN WHICH EACH OF THE ABOVE ITEM APPEAR JUST ONCE, AND THE APPEARING ONE SHOULD BE THAT ONE WHICH HAS THE LATEST DATE WITHIN AN ITEM I.E.

Item ID Date Quantity
123 05-Aug 454
124 08-Aug 65
125 09-Aug 2.

LIKE ITEM NO. 123 WAS ISSUED 5 TIMES AND THE VERY LATEST IT WAS ISSUED WAS ON 05-AUG AND SO THAT RECORD IS APPEARING ON THE ABOVE REPORT.

I HOPE I AM ABLE TO EXPLAIN MY REQUIREMENT, IF NOT PLEASE LET ME

**tigeravatar** · 07-31-2012, 01:25 PM

habibian88,

In the future, please don't use all caps submitting a new thread.
Attached are two example workbooks based on the criteria you described. The first is the .xls (2003 and lower) version, and the second is the .xlsx (2007 and higher) version. I included two versions because they use different formulas depending on what version you're using.

In both workbooks, the data setup is identical. The table you provided with all of the data is in columns A, B, and C. The resulting table is in columns E, F, and G. Row 1 is headers, and row 2 is where the actual data/formulas start.

In the .xls (2003 and lower) version:

Cell E2 and copied down to E6 is this formula to get the unique Item IDs:
```
Please Login or Register  to view this content.
```
Cell F2 and copied down to F6 is this formula to get the latest date:
```
Please Login or Register  to view this content.
```
Cell G2 and copied down to G6 is this formula to get the quantity of that Item ID and Date:
```
Please Login or Register  to view this content.
```

In the .xlsx (2007 and higher) version:

Cell E2 and copied down to E6 is this formula to get the unique Item IDs:
```
Please Login or Register  to view this content.
```
Cell F2 and copied down to F6 is this formula to get the latest date:
```
Please Login or Register  to view this content.
```
Cell G2 and copied down to G6 is this formula to get the quantity of that Item ID and Date:
```
Please Login or Register  to view this content.
```

**habibian88** · 08-01-2012, 02:22 AM

Thanks a million, it really worked..

**Cutter** · 08-01-2012, 08:18 AM

@ habibian88

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.