Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Frequency Sorted Unique Values (2002)

    Please help with VBA code to:
    Extract unique values from a specified Range, Count frequency of occurrence, Sort values by descending frequency to a specified Cell. Attached file shows needed end result. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Frequency Sorted Unique Values (2002)

    Try this:

    Sub UniqueList()
    Dim rListPaste As Range
    Dim rDatabase As Range

    Set rListPaste = Application.InputBox _
    (Prompt:="Please select the destination cell", Type:=8)

    If rListPaste Is Nothing Then
    MsgBox "No range nominated.", vbExclamation
    Exit Sub
    End If

    Set rDatabase = Range("A1", Range("A65536").End(xlUp))
    'May need to specify [NameofSheet].Range, e.g, Sheet1.Range
    rDatabase.AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=rListPaste.Cells(1, 1), Unique:=True

    ' Extend rListPaste to range of unique values
    Set rListPaste = Range(rListPaste.Cells(1, 1), _
    rListPaste.Cells(1, 1).End(xlDown))
    ' Set frequency formulas
    rListPaste.Offset(0, 1).FormulaR1C1 = _
    "=COUNTIF(" & rDatabase.Address(ReferenceStyle:=xlR1C1) & ",RC[-1])"
    ' Create column header
    rListPaste.Cells(1, 2).Value = "Frequency"
    ' Sort range
    rListPaste.Resize(ColumnSize:=2).Sort _
    Key1:="Frequency", Order1:=xlDescending, Header:=xlYes
    End Sub

  3. #3
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frequency Sorted Unique Values (2002)

    Thank you very, very much. (Could not thank you earlier. I experienced difficulties accessing this webpage.)
    Selva.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Frequency Sorted Unique Values (2002)

    <hr>I experienced difficulties accessing this webpage<hr>
    The Lounge moved to a new server on December 29, and it takes some time for all the Domain Name Servers on the internet to become aware of the new address. Some still aren't, I think.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •