CODEWITHSUNDEEP
excelfunctionexcel-formulaformularanking
Jmar
Jmar

Reputation: 21

Using Excel function: How to Select Rows based on Max Version Number

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!

Upvotes: 1

Views: 124

Answers (4)

Jmar
Jmar

Reputation: 21

Happy New Year ~ With guidance, I took the recommendations and altered it to combine it with another filter to toggle between a progress of completed or less than 100%.

=FILTER(Table2,(COUNTIFS(Table2[Item '#],Table2[Item '#],Table2[Version '#],">"&Table2[Version '#])=0)*(Table2[Progress Category]=B3))

in the filter function, if you need to add another criteria you can use the * asterisk and add in the next item you need to filter! And to toggle, I used Cell B3 to have a toggle between a list of progress options. Exciting, I know. Thanks everyone!

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34370

Also (old-school):

=FILTER(Table1,COUNTIFS(Table1[Item '#],Table1[Item '#],Table1[Version '#],">"&Table1[Version '#])=0)

Upvotes: 1

P.b
P.b

Reputation: 11628

=CHOOSEROWS(Table2,XMATCH(UNIQUE(Table2[Item '#]),Table2[Item '#],,-1))

Or if the table is unsorted:

=LET(x,SORT(Table2,{2,1}),
     i,TAKE(x,,1),
CHOOSEROWS(x,XMATCH(UNIQUE(i),i,,-1)))

Upvotes: 2

pgSystemTester
pgSystemTester

Reputation: 9932

This formula does the following:

  • Sorts by ID then version
  • Creates unique list of ID's
  • Looks up the ID without exact match, which will always return the last version
  • Returns respective row

I copied your sample data to cell A1

=LET(allData,SORT($A$2:$C$11,{1,2},1),zUnique,UNIQUE(INDEX(allData,,1)),
iPullRow,MATCH(zUnique,INDEX(allData,,1)), HSTACK(zUnique,INDEX(allData,iPullRow,{2,3})))

Sample result

Upvotes: 1

Related Questions