I created a formula to filter a list of items if the progress completion of a build is 100% complete. However some items have revisions and then will eventually restart their progress completion.
The issue I'm encountering is that my filter function will pull in all the versions of an item. How would I get the filter function to only return the rows of the latest version. Right now, the filter is basing it off <100% but there are still duplicates of the item #.
The formula I am using: =(FILTER(Table2,Table2[Progress Category]="<100%"))
Example of the raw data:
| Item # | Version # | Progress % |
|---|---|---|
| 1 | 1 | 100% |
| 2 | 1 | 100% |
| 2 | 2 | 80% |
| 3 | 1 | 100% |
| 3 | 2 | 100% |
| 3 | 3 | 90% |
| 4 | 1 | 100% |
| 5 | 1 | 100% |
| 5 | 2 | 80% |
| 6 | 3 | 100% |
Expected output (1 item with the latest version and the latest progress %):
| Item # | Version # | Progress % |
|---|---|---|
| 1 | 1 | 100% |
| 2 | 2 | 80% |
| 3 | 3 | 90% |
| 4 | 1 | 100% |
| 5 | 2 | 80% |
| 6 | 3 | 100% |
Thank you!
Also (old-school):
=FILTER(Table1,COUNTIFS(Table1[Item '#],Table1[Item '#],Table1[Version '#],">"&Table1[Version '#])=0)