1

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:

https://docs.google.com/spreadsheets/d/1WqDaiNK-capSjp0l1-vJDJH30Z4pjA74y1U5M7c7BmQ/edit?usp=sharing

enter image description here

Improve this question
4
  • Please share a link to the sample spreadsheet shown above so that the volunteer contributors here don't need to open a new spreadsheet themselves and hand-enter your test data.
    – Erik Tyler
    May 18 at 23:27
  • Thanks, I've added it to the original post as well and will link it in this comment as well. docs.google.com/spreadsheets/d/…
    – Sp3ctral
    May 18 at 23:38
  • 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 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 at 5:11

2 Answers 2

Reset to default
0

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

=ArrayFormula(SORT({UNIQUE(FILTER(A:A,A:A<>"")),IFERROR(VLOOKUP(UNIQUE(FILTER(A:A,A:A<>"")),SORT(FILTER(A:B,A:A<>""),TEXT(REGEXEXTRACT(FILTER(B:B,A:A<>""),"\d+$"),"00"),0),2,FALSE))}))

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.

Improve this answer
2
  • Wow this is super robust, I would've never thought to do it this way. Looks like it works perfectly for my real data as well with a few tweaks. Thanks a ton!
    – Sp3ctral
    May 19 at 5:36
  • Glad it worked as planned. If you would, please take a moment to mark the answer post as "Best Answer"; this allows the volunteer contributor community here to know which posts have been fully answered as well as making it easier for future site visitors to find the answer if it matches their own search criteria.
    – Erik Tyler
    May 19 at 8:17
0

My sheet.

First column uses: =UNIQUE(A:A)

Second column uses:

="test" & SORT(VALUE(REGEXREPLACE(FILTER(B:B,A:A=G1),"test","")),1,FALSE)

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.

Improve this answer
1
  • 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 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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