Top 3 from list

[cecil]

I have a list of numbers. I want the top (largest) 3. The list reads 42,42,39,32,32,32,32,29, etc. If I use the forumula "Large(range,1)" and "Large(range,2)" I get 42,42. I want 42,39. In other words, I want the largest 3 unique numbers from the list.

[HansV]

This is not so easy in Excel. Let's say your list is in A1:A100.
Enter the following formula in B1:

=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

and fill down to B100.
The list in column B will suppress the duplicates.
Enter the following formula in C1:

=LARGE(B$1:B$100,ROW())

and fill down to C3.
This will return the three largest unique numbers.

[cecil]

Thanks. In this case, I cannot utilize the extra column and that solution will not work in an array formula.

Oh well. It is custom formula time.

Thanks.

[HansV]

You could use this:

Code: Select all

Function LargeUnique(rng As Range)
  Dim col As New Collection
  Dim i As Long
  On Error Resume Next
  For i = 1 To rng.Count
    col.Add Item:=rng(i), Key:=CStr(rng(i))
  Next i
  BubbleSortDesc col
  ReDim retval(1 To col.Count)
  For i = 1 To col.Count
    retval(i) = col(i)
  Next i
  LargeUnique = retval
End Function

Sub BubbleSortDesc(ByRef col As Collection)
  Dim varTemp As Variant
  Dim i As Long
  Dim j As Long
  For i = 1 To col.Count - 1
    For j = i + 1 To col.Count
      If col(i) < col(j) Then
        varTemp = col(j)
        col.Remove j
        col.Add varTemp, CStr(varTemp), i
      End If
    Next j
  Next i
End Sub

To list the three unique values in three cells next to each other, select the cells and enter this array formula (confirm with Ctrl+Shift+Enter):

=LargeUnique(A1:A50)

To list the three unique values in three cells below each other, select the cells and enter this array formula (confirm with Ctrl+Shift+Enter):

=TRANSPOSE(LargeUnique(A1:A50))

where A1:A50 is the list of values.

[cecil]

Cool. I did not expect you to write the code for me. Thanks! I will try it in the morning.