Unique Values

    Hi All,


    i was wondering if there is a function in Excel that counts all the unique values from a list that meet a given criteria?


    ie Count the unique items in column D that have and x in the offsetted cell in column C?


    Is there anything better than the below (extremely slow) custom function that is by no means perfect


    Public Function CountSingle(InputRange As Range, Criteria1 As String, _
    InputOffset1 As Integer, Criteria2 As String, InputOffset2)
    Dim c As Range, counter As Double
    For Each c In InputRange
    If c.Offset(0, InputOffset1).Value = Criteria1 Then
    If c.Offset(0, InputOffset).Value = Criteria2 Then
    If c.Value <> c.Offset(1, 0).Value Then
    counter = counter + 1

    End If
    End If
    End If
    Next c


    CountSingle = counter
    End Function



    Any assistance would be highl appreciated.


    DW

    Generating a pivot table from your data may (XL-Dennis is right, can't know without the actual data/application intent in hand) give you what you want. Use the data column for sorting and put the criteria into the page field so that you can easily look only at the data that match the correct criteria. Put anything you want in the data field (although counting something sounds like it might be useful for your application).

    Decided my first post was a little short if you aren't familiar with pivot tables. I made up a quick example (attached). Note the drop-down in at the top of the Pivot table that allows you to select the country you want to look at. Note also that a Joe in England is diostinguished from the Joe in USA, and that it tells you that Sally is listed twice in England.

    Sorry Guys for not priovidig an example..


    I have one attached if you wouldn.t mind looking at it...


    I hope it's enough to look at..


    I have used the custom function above to count the uniue values

    I am having no luck at unzipping your file. (and on 84K downloads -- your post shows 253K).


    Can you post/attach an Excel file. (Max size attachment that OsGrid will accept is 1Meg, but an example doen't need to be very large at all.)


    FYI: I'm disappearing for a long weekend, but I expect you'll get lots of help before I get back on Tuesday.

    Here is a round about method. I first added a colume to your Jan data and then sorted it by Document then OrdRs then SaTy and used the following formula (below is the cell D2 version) to flag (=1) unique numbers.


    =IF(AND(A2=A1,B2=B1),0,1)


    The Pivot table them uses this new column to give you the result I think you are after.


    Hope this helps.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login