Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would like to extract and count distinct values from a range and sort by the Pareto. If I were using a DB this would be easy.

    Select column2, column3, count(column3) from table where some criteria order by count(column3)

    Is there a way to do this in Excel without macro's? If not, I can write the macro.

    Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If the source workbook is closed, then you can use ADO to query it:
    [pre]

    ' Sample demonstrating how to return a recordset from a workbook
    Dim strQuery As String, rst As Object, strConn As String
    Dim varData As Variant
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Misc\ADO_test2.xls;" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"""
    strQuery = "SELECT Test, COUNT(Test2) FROM [Sheet1$] GROUP BY Test ORDER BY COUNT(Test2);"
    Set rst = CreateObject("adodb.Recordset")
    rst.Open strQuery, strConn, 3, 1, 1 'adOpenStatic, adLockReadOnly, adCmdText
    ActiveSheet.Range("A2").CopyFromRecordset rst
    rst.Close
    Set rst = Nothing
    [/pre]

    for example. You can do the same with an open workbook but there are memory leaks so if you do more than a couple of queries you can run into problems.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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