2

I have two tables that contain different information, but both have a field called 'Model'. I want to know how many distinct types of model there are in both tables.

I can easily find out the number of distinct types for each table on its own using the formula below, which I found here.

=SUMPRODUCT((table[field]<>"")/COUNTIF(table[field],table[field]&""))

However I cannot think of a way to do the same thing for two separate lists that I want to combine into a single column for use in a single equation?

1 Answer 1

1

Try this short User Defined Function:

Public Function Kount(ParamArray Rng()) As Long
    Dim i As Long, r As Range, c As Collection, rr As Range

    Set c = New Collection

    On Error Resume Next
        For i = LBound(Rng) To UBound(Rng)
            Set r = Rng(i)
            For Each rr In r
                c.Add rr.Value, CStr(rr.Value)
            Next rr
        Next i
    On Error GoTo 0

    Kount = c.Count
    Set c = Nothing
End Function

It will calculate the number of distinct entries across any number of disjoint ranges:

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .