Count unique values in column based on another column (Microsoft Excel 2013)

Question

For a project, I'm creating an Excel macro to count unique column values based on another column value. Here is a basic example of the macro I'm trying to create:

Data

col_1	col_2
a	x
a	y
b	z
b	z

Macro

Sub Main()
    Dim Param As String
    Param = "a"
    MsgBox UniqueValues(Param)
End Sub

Function UniqueValues(Param As String) As String
    Dim EvaluateString As String
    EvaluateString = "=SUM(--(LEN(UNIQUE(FILTER(B:B,A:A=" & """" & Param & """" & ","""")))>0))"
    UniqueValues = Evaluate(EvaluateString)
End Function

Expectation

The expectation is that for Param = "a" the function returns 2 and for Param = "b" it returns 1.

Issue

Even though function works perfpectly in Excel for Microsoft 365 Apps for Enterprise, the project requires me to use Excel for Microsoft Office Standard 2013. This version doesn't support the use of the UNIQUE and FILTER functions used in EvaluateString.

I want to understand if there's a simple way to count the unique values in a column based on a value in another column in Excel for Microsoft Office Standard 2013. Your help is much appreciated.

Answer 1

You can use the array formula

=SUM(IF($A$1:$A$5="a",1/COUNTIFS($A$1:$A$5,"a",$B$1:$B$5,$B$1:$B$5)),0)

After entering the formula, instead of Enter, you need to press Ctl + Shift + Enter

In VBA, the above formula can be used as shown below

Option Explicit

Sub Main()
    Dim Param As String
    Param = "b"
    
    MsgBox "The count for " & Param & " is " & UniqueValues(Param)
End Sub

Function UniqueValues(Param As String) As String
    Dim EvaluateString As String
    Dim ws As Worksheet
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    'SUM(IF(Sheet1!A1:A5="a",1/COUNTIFS(Sheet1!A1:A5,"a",Sheet1!B1:B5,Sheet1!B1:B5)),0)
    EvaluateString = "SUM(IF($A$1:$A$5=" & _
                     Chr(34) & Param & Chr(34) & _
                     ",1/COUNTIFS($A$1:$A$5," & _
                     Chr(34) & Param & Chr(34) & _
                     ",$B$1:$B$5," & _
                     "$B$1:$B$5)),0)"
                     
    UniqueValues = ws.Evaluate(EvaluateString)
End Function

In Action

Answer 2

When your data are in "Sheet1", columns A and B, starting in row 1, you can use this macro (results in columns D and E):

Sub macro1()
Dim a As Integer, p As Integer, x As Integer, y As Integer
a = 0: p = 0: x = 1: y = 1
With Sheets("Sheet1")
.Columns("d:e").ClearContents
Do Until x > .Cells(.Rows.Count, 1).End(xlUp).Row
a = 1
Do While .Cells(x, 1) = .Cells(y, 1)
If .Cells(x, 2) <> .Cells(y, 2) Then a = a + 1
x = x + 1
Loop
p = p + 1
.Cells(p, 4) = .Cells(y, 1)
.Cells(p, 5) = a
y = x
Loop
End With
End Sub