CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Shawn White
Shawn White

Reputation: 41

Using Unique and Highest Entry in Column

I created a check-in/check-out system for equipment using a form. I'd like to pull data from the Entries tab to the Database tab. Id like the Database tab to contain ALL unique values in column C based on the highest value in Column A. Here is the formula I'm using that is not working:

=ARRAYFORMULA(IFerror(VLookup(Unique(sort(Entries!C2:C)),Sort({Entries!C2:C,Entries!A2:G},4,1,2,0),{2,3,4,5,6,7,8},0),))

Here is a link to the sheet. The Results tab is my desired results. https://docs.google.com/spreadsheets/d/1ChaLBNtDJwTl73nMaCkKqh7DT-TuVLt8wm7Yj0KQLMM/edit?usp=sharing

Upvotes: 1

Views: 31

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

The easy way to do these is with SORTN:

=sortn(sort(Entries!A2:G,1,0),999,2,3,1)

so first sort descending on Entry ID so that the latest ID's come first, then sort ascending on Equipment specifying 'remove duplicates' so that only the first (latest) ID is kept for each Equipment value. '999' should be increased if there are more than 1000 rows in your sheet.

enter image description here

Your formula also works, except that the dates come out as numbers and have to be formatted separately

Upvotes: 0

Related Questions