Count unique values from 2 columns based on criteria

[Villalobos]

Hello,

I would like to ask that how is it possible to count the unique values based on criteria if the repeating values are in 2 columns (unfortunately, I can count only that case if the repeating values are in 1 column)?

Additional info: the range of repeating values (sheet Evaluation R9:S lastrow) is dynamic

The sample file with the desired results has been added to this thread.

Thanks in advance the reply!

 

[Marc L]

Hi,

could be easily done with Excel basics worksheets functions like filter,
advanced filter, subtotal but by correcting criterias in Complexity sheet …

'Cause (with dot as space) "PM.1" is not "PM..1" in Evaluation worksheet !
​

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Villalobos]

Hello,

Thank you the help!

@Marc L Could you please show me an example how to do it?

@Narayan Is it possible to count without helper columns?

 

[NARAYANK991]

Hi ,

It may be possible , but I don't think I can do it.

Narayan

 

[Marc L]

As written, with Excel basics :​

Sub Demo1()
                                     Dim Rg As Range
                                     Set Rg = Range("Complexity!Z1")
                                         VA = Rg.Parent.[C8].CurrentRegion.Value
                 Application.ScreenUpdating = False
Rg.Parent.[N9].Resize(UBound(VA) - 1).Value = 0

With Range("Evaluation!E8").CurrentRegion
    For R& = 2 To UBound(VA)
        .AutoFilter 1, VA(R, 1)

        If .SpecialCells(xlCellTypeVisible).Count > 1 Then
            With .Parent.[R8].CurrentRegion
                 .Columns(1).Copy Rg
                 .Columns(2).Offset(1).Copy Rg.End(xlDown)(2)
            End With

            With Rg.CurrentRegion
                .AdvancedFilter xlFilterInPlace, , , True
                .Parent.Cells(R + 7, 14).Value = .SpecialCells(12).SpecialCells(2, 1).Count
                .Parent.ShowAllData
                .Clear
            End With
        End If
    Next
         Set Rg = Nothing
    If .Parent.FilterMode Then .AutoFilter
End With
End Sub

Instead of using SpecialCells method, worksheet function
SUBTOTAL(103 or 102 could be used …

Do you like it ? So thanks to click on bottom right Like !​

 

[Marc L]

Is it possible to count without helper columns?

Always with Excel basics, this time using MATCH worksheet function
with a local dictionary of unique values (UV array variable) :​

Sub Demo2()
Dim Rg As Range
                        VA = Range("Complexity!C8").CurrentRegion.Value
Application.ScreenUpdating = False

With Range("Evaluation!E8").CurrentRegion
    For R& = 2 To UBound(VA)
        C& = 0
        .AutoFilter 1, VA(R, 1)

        With .Parent.[R8].CurrentRegion.SpecialCells(xlCellTypeVisible)
            If .Count > 2 Then
                ReDim UV(1 To .Count)

                For Each Rg In .Areas
                    For Each V In Rg.Value
                        If IsNumeric(V) Then If IsError(Application.Match(V, UV, 0)) Then C = C + 1: UV(C) = V
                    Next
                Next
            End If
        End With

        Range("Complexity!N" & R + 7).Value = C
    Next

    If .Parent.FilterMode Then .AutoFilter
End With
End Sub

You like ? So thanks to …
​

 

[Villalobos]

You are AWESOME Marc L!

 

[Marc L]

Obrigado ! (Thanks !)

But first demonstration code operations could be done in Excel
by everyone ('cause it's only basics !) and just by activating
Macro recorder, anyone gets a free code skeleton ! Like I did …
Then at this point it requires a few VBA skills to amend it.
​