Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have data in two columns, sorted by the second column. As follows:

A, 1
D, 2
B, 4
A, 6
B, 7
...

I need to filter it so that the first column is unique. I.e. the value in the first column only exists once in the result.

A, 1
D, 2
B, 4

I am keeping track of scores and would like to create a "top 10" list of all of the data. I can sort the data but I have no idea how (if it is possible) to filter out everything but the personal best result from each participant.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

For the first column, in column D for example, use the following formula:

=UNIQUE(A1:A5)

and the second column, in column E for example:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D;A:B;{2}*SIGN(ROW(A:A));0),""))
share|improve this answer
    
Thanks, it works like a charm. I understand everything, except why the third parameter to VLOOKUP has to be "{2}*SIGN(ROW(A:A))". Can you explain that? –  Torben May 28 '13 at 7:47
1  
It is used to make the ARRAYFORMULA work in combination with the VLOOKUP. See goo.gl/96fdj and goo.gl/Fi02r –  Jacob Jan Tuinstra May 28 '13 at 7:53
    
@Torben Forgot to hail you !! –  Jacob Jan Tuinstra May 28 '13 at 8:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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