I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's
 Pfreq lists frequencies of input combinations: Examples:
 Please note that I do not recommend to use the Pfreq(Pfreq(...)) approach for more than some thousand rows: For 10,000 rows and two columns it takes more than 1 minute to calculate on my dual core computer! If you need a sorted output you can take my UDF GSort. Another sort example you will find here. Another Pfreq example where you can see why I do not recommend clever individual worksheet (array) formulas is shown here: The task was to create a statistic on engineers - how many different customers they served per month. Have a look at the 160KB Excel 2007 © sample file but open and use at your own risk, please read my disclaimer.
 Function Pfreq(ParamArray v()) As Variant'Pfreq lists how often each value combination'in paramarray appears. Example:'    A     B' 1 Smith Adam' 2 Myer Ben' 3 Smith Ben' 4 Smith Adam' 5 Myer Ben'Now select C1:E3 and array-enter'=Pfreq(A1:A5,B1:B5) and you will get'    C   D    E' 1 Smith  Adam  2' 2 Myer   Ben   2' 3 Smith  Ben   1'Reverse("moc.liborplus.www") V0.4 15-Oct-2009Dim obj As ObjectDim vR As VariantDim i As Long, j As Long, k As Long, lvdim As LongDim s As String, sC As StringWith Application.WorksheetFunctionsC = "|"Set obj = CreateObject("Scripting.Dictionary")k = 0v(0) = .Transpose(.Transpose(v(0)))lvdim = UBound(v(0))If lvdim > 100 Then lvdim = 100On Error GoTo ErrHdl 'Please read                               'http://www.sulprobil.com/Get_it_done/IT/Excel_Fun/Excel_VBA/Error_Trapping/error_trapping.htmlReDim vR(0 To UBound(v) + 1, 1 To lvdim)For i = 1 To UBound(v(0))    s = v(0)(i, 1)    For j = 1 To UBound(v)        v(j) = .Transpose(.Transpose(v(j)))        s = s & sC & v(j)(i, 1)    Next j    If obj.Item(s) > 0 Then        vR(UBound(v) + 1, obj.Item(s)) = vR(UBound(v) + 1, _                                        obj.Item(s)) + 1    Else        k = k + 1        obj.Item(s) = k        For j = 0 To UBound(v)            vR(j, k) = v(j)(i, 1)        Next j        vR(UBound(v) + 1, k) = 1    End IfNext iIf k > 0 Then ReDim Preserve vR(0 To UBound(v) + 1, 1 To k)Pfreq = .Transpose(vR)End WithExit FunctionErrHdl:If Err.Number = 9 Then    If i > lvdim Then        'Here we normally get if we breach Ubound(vR,2)        'So we need to increase last dimension        lvdim = 10 * lvdim        If lvdim > UBound(v(0)) Then lvdim = UBound(v(0))        ReDim Preserve vR(0 To UBound(v) + 1, 1 To lvdim)        Err.Number = 0        Resume 'Back to statement which caused error    End IfEnd If'Other error - terminateOn Error GoTo 0ResumeEnd Function
 And here is yet another Pstat/Pfreq/GSort example: Count unique project names by type if there is a date. Have a look at the 208KB Excel 2003 © sample file but open and use at your own risk, please read my disclaimer.