I was wondering if there's a way to use a function (maybe Filter?) in order to sort this data such that column K only contains one of each number, and the highest test associated with it in column L. Here's an example that I created manually using a small section of data.

For example, the highest test associated with the number 9 is test 11, so the function should return only one entry containing 9, and then the highest test (test11) in the cell next to it. I feel this should be fairly easy using a filter function but I cant quite get it to work. Thanks for any help!

Link to sample spreadsheet:


enter image description here

  • Few things I'd like clarification on: There are two 4's in the sample output, is that suppose to happen? Also will the values have the word "test" appended on the start always?
    – David Tan
    May 19, 2022 at 2:17
  • @DavidTan Sorry about that, there were not supposed to be two 14's in the sample output. I've corrected that in the sample sheet as well as the post. Also yes, the word "test" is always there but I plan on removing it, maybe even as part of the function.
    – Sp3ctral
    May 19, 2022 at 5:11

2 Answers 2


Delete everything in your sample sheet's Sheet1 Cols D and E. Then place the following formula in D1:


I've written this assuming that it does not exactly represent your real data. So there are elements to this formula that your real data may not require, but which I've included as safeguards to cover a variety of possible scenarios.

In plain English, the formula creates a unique listing of non-null Col-A values in the first column of a new array. In the second column of that array, the same values formed for the first column are looked up within a third virtual array made up of the original values in Cols A and B, sorted by Col A in ascending order first and by the numeric end portion of Col B in descending order (highest at the top), after those numeric portions have been normalized to contain two characters (e.g., 01, 03, 14, etc.). Because the highest values will always be at the top of this virtual array's second column, they'll always be found first and returned.


My sheet.

First column uses: =UNIQUE(A:A)

Second column uses:


To quickly explain, the first column just pulls all unique value so that we have a reference.

Next the second column first finds all with the value from the first column using the FILTER(B:B,A:A=G1). Next we remove the 'test' to sort easily using REGEXREPLACE, convert to a number value using VALUE then finally sort by descending order. We concatenate that with "test" to place it back. Even if the sort function gives back an array, concatenating it with "test" will end up giving you the first value only.

This method is definitely not as robust as Erik's is, but I was already working on this when he answered.

  • Might not be quite as robust as Erik's but it seems to work equally well for my data set. If I also wanted to remove "test" from each entry, leaving only the number, I figure I could just do something like: ="" & SORT(VALUE(REGEXREPLACE(FILTER(B:B,A:A=G1),"test","")),1,FALSE) Do you think that'd probably be the easiest way to go about doing that? Thanks again for the help!
    – Sp3ctral
    May 19, 2022 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.