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

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.

CodePudding user response：

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

In Action

CodePudding user response：

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