How to get a frequency count when first and last names are in dif columns in excel

Question

I have an Excel 2010 workbook with one row for Last name and one row for first name. There are approximately 1800 entries.

The same person (first and last name) can appear multiple times- jim simith appears 5 times. I want to do a frequency count of how many times jim simth appears in the list.

I used =COUNTIF($B$2:$B$1800,B2) , where B is the last name. The problem is that there are other Smiths in the list that are counted because the first name is in a separate column. Does anyone know how I could do a frequency count of each individual where their last names can be the same?

Answer 1

Probably overkill, but you can create an ADODB connection to the worksheet:

Dim conn As New Connection
With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=""" & ActiveWorkbook.FullName & """;" & _
        "Extended Properties=""Excel 12.0;HDR=No;"""
    'If you're running a version of Excel earlier than 2007, the connection string should look like this:
    '.ConnectionString = "Data Source=""" & ActiveWorkbook.FullName & """;" & _
    '    "Extended Properties=""Excel 8.0;HDR=No;"""
    .Open
End With

Then you can issue an SQL statement against the worksheet:

Dim rs As Recordset
Set rs = conn.Execute( _
    "SELECT F2 AS LastName, F1 AS FirstName, Count(*) AS C " & _
    "FROM [Sheet1$A$2:$B$1800] " & _
    "GROUP BY F2, F1" _
)

Answer 2

=COUNTIFS($B$2:$B$1800,B2,$A$2:$A$1800,A2)

Answer 3

I haven't tested this, and I'm assuming your COUNTIF formula works.

In a new column, you can CONCATENATE the strings in the columns and run your COUNTIF on that.

Assuming that A is first name and B is last name, in C you'd have something like:

=CONCATENATE(A2, " ", B2)

And then you'd do the COUNTIF on that:

=COUNTIF($C2:$C1800,$C2)

Optionally, you may hide column C if you don't want it displayed.