• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count unique values from 2 columns based on criteria

V

Villalobos

Active Member
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!
 

Attachments


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 !
​
 

As written, with Excel basics :​
Code: 
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 !​
 
Last edited:
Villalobos said:
Is it possible to count without helper columns?
Click to expand...
Always with Excel basics, this time using MATCH worksheet function
with a local dictionary of unique values (UV array variable) :​
Code: 
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 …
​
 

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.
​
 
You must log in or register to reply here.
Back
Top