Counting Multi Criteria Occurrences in a Group of Records - vba

[Nicha]

Group	B	C	Result
A1	A	123
A1	#	0
A1	A	546
A1	#	9
A2	A	678	678
A2	A	678	678
A2	#	5	678
A3	A	532	532
A3	#	0	532
A3	#	0	532

Regarding the data presented above, I need to Count occurrences on columns B and C, inside each Group, in order to get the value in [C] to fill the column [D] with it.
the idea is to count the combinations of the 'A' values in column B, with the numerical values in column C - in 'A' + Value combinations - using the below criteria's:

• If there's only one 'A' in column B, the value to fill in column D is the correspondent value in the column C. This is the case of the Group (A3) with the value (532). Obviously, If this 'A' has no value on C, theres nothing to fill in column D.
• If there's 2 or more 'A' in Column B with the same values in column C, the value for filling in column D is the one that result from that combination. This is the case of Group (A2) with value (678).

Note : The Group (A1) will not be filled because, although it has 2 'A's, they have different combinations since they have divergent values in column C.

This process could be a vba Function that I'll include inside the main(), that will check those 2 combinations, and if the function returns no value - case of Group (A1) - the Main() process will perform analisys on those groups, reading them line by line.

Can anyone help please?

 

[Nicha]

The problem is twofold. Firstly, the Minifs() and Maxifs() functions didn't appear until 2019 - sorry, I didn't notice you were on 2016 (my mistake). Secondly, the current UDF wouldn't return anything for A5 in you example in post #9 because I understood that if there was a clash of values (that were legitimate values i.e. had an A in column B) then no value should be returned (see demo below with the current UDF).

UDF test.xlsm
	A	B	C	D
1	Group	B	C	Result
2	A1	A	123
3	A1	#	0
4	A1	A	546
5	A1	#	9
6	A2	A	678	678
7	A2	A	678	678
8	A2	#	5	678
9	A3	A	532	532
10	A3	#	0	532
11	A3	#	0	532
12	A4	#	1000	10
13	A4	A	10	10
14	A5	A	20
15	A5	A	35
16	A5	A	20
Sheet1

Cell Formulas
Range	Formula
D2:D16	D2	=GroupA()
Dynamic array formulas.

So two things to fix. 1) I'll need to write a different UDF that only uses functions available in 2016, and 2) could you please clarify what should happen if, for example, there are 4 rows in the same group, all with an A in column B, but the values in column C are paired - in other words for example, there are 2 x 10 and 2 x 100 - which value (if any) should be returned in column C? I'll need the second point clarified before I can proceed, which won't be until later today my time.

Many thanks Kenny9999. Everytime we detect a paired match, like the one you described, there's no result possible. That is happening in group A1, where's a tie too, but with only occurrence.
The mission would be mutch more easier with SQL in a database. We could group by columns A, B and C, count the occurrences, and chose the higher value.
Manny thanks once more fir your precious time.

 

[kevin9999]

Many thanks Kenny9999. Everytime we detect a paired match, like the one you described, there's no result possible.

Thanks for the clarification. As I said, I'll work on this later today local time. (not sure when I became Kenny though.. )

 

[Nicha]

Thanks for the clarification. As I said, I'll work on this later today local time. (not sure when I became Kenny though.. )

Sorry Kevin9999.

 

[kevin9999]

See if the following does what you want it to (should be compatible with 2016).

Function GroupA() As Variant
    Dim s As String, r As Range, LRow As Long, i As Long, a, x
    s = Application.Caller.Offset(, -3).Value
    LRow = Columns("A").Find(s, , xlFormulas, , 1, 2).Row
    Set r = Range(Cells(Application.Caller.Row, 3), Cells(LRow, 3))
    a = r
    
    'Get value if single "A" in group
    If WorksheetFunction.CountIf(r.Offset(, -1), "A") = 1 Then
        For i = 1 To UBound(a, 1)
            If r.Cells(i).Offset(, -1) = "A" Then
                x = r.Cells(i).Value2
                Exit For
            End If
        Next i
    Else
        'Get value if more than one "A" and matching value in group
        For i = 1 To UBound(a, 1)
            If WorksheetFunction.CountIfs(r.Offset(, -1), "A", r, r.Cells(i, 1)) > 1 Then
                x = r.Cells(i).Value2
                Exit For
            Else
                x = vbNullString
            End If
        Next i
    End If
    
    'Final test if more than one 'pair' of values in group
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a, 1)
            If r.Cells(i).Offset(, -1) = "A" And _
            WorksheetFunction.CountIfs(r.Offset(, -1), "A", r, r.Cells(i, 1)) > 1 Then _
            .Item(a(i, 1)) = .Item(a(i, 1))
        Next i
        If .Count > 1 Then x = vbNullString
    End With
    
    For i = LBound(a, 1) To UBound(a, 1)
        a(i, 1) = x
    Next i
    GroupA = a
End Function

UDF test.xlsm
	A	B	C	D
1	Group	B	C	Result
2	A1	A	123
3	A1	#	0
4	A1	A	546
5	A1	#	9
6	A2	A	678	678
7	A2	A	678	678
8	A2	#	5	678
9	A3	A	532	532
10	A3	#	0	532
11	A3	#	0	532
12	A4	#	1000	10
13	A4	A	10	10
14	A5	A	20	20
15	A5	A	35	20
16	A5	A	20	20
17	A6	A	5
18	A6	A	5
19	A6	A	100
20	A6	A	100
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=GroupA()
Dynamic array formulas.

 

[Nicha]

Hello Kevin9999
I tested your function and it is working in all cases. But if I change the group you created (A6), where we have 2 pairs of 'A', we should choose the one with the more frequent value (5). And this applies to situations where we have 2, or more pairs, we have to choose the combination that is more frequent, i.e., has a higher number of occurrences.

The function can return only the value that was elected; I think it will be easier...

Group	B	C	Result
A1	A	123
A1	#	0
A1	A	546
A1	#	9
A2	A	678	678
A2	A	678	678
A2	#	5	678
A3	A	532	532
A3	#	0	532
A3	#	0	532
A4	#	1000	10
A4	A	10	10
A5	A	20	20
A5	A	35	20
A5	A	20	20
A6	A	100	5
A6	A	5	5
A6	A	100	5
A6	A	5	5
A6	A	5	5

P.s. Can you tell me please, why you get the (.Value2) instead of (.Value) in code below?

x = r.Cells(i).Value2

I appreciated very much your solution. seems to be quite close to the final solution. It remains to identify, in groups where there are 2 or more pairs, the ones that occur most often regardless of the order in which they appear in the table. The actual table has about 600,000 records.

 

[kevin9999]

It remains to identify, in groups where there are 2 or more pairs, the ones that occur most often regardless of the order in which they appear in the table.

A job for tomorrow

Also:
What is the difference between .text, .value, and .value2?

 

[kevin9999]

I think we're getting close...

Function GroupX() As Variant
    
    'Set Variables
    Dim s As String, r As Range, LRow As Long, i As Long, j As Long, a, x, y, z
    s = Application.Caller.Offset(, -3).Value
    LRow = Columns("A").Find(s, , xlFormulas, , 1, 2).Row
    Set r = Range(Cells(Application.Caller.Row, 3), Cells(LRow, 3))
    a = r

    '1. If no "A"s in group
    If WorksheetFunction.CountIf(r.Offset(, -1), "A") = 0 Then
        x = vbNullString
        GoTo FillArray
    End If
    
    '2. If only one "A" in group
    If WorksheetFunction.CountIf(r.Offset(, -1), "A") = 1 Then
        For i = 1 To UBound(a, 1)
            If r.Cells(i).Offset(, -1) = "A" Then
                x = r.Cells(i).Value2
                GoTo FillArray
            End If
        Next i
    End If
    
    '3. If more than one "A" in group
    If WorksheetFunction.CountIfs(r.Offset(, -1), "A", r, ">0") > 1 Then
        z = 0
        For i = 1 To UBound(a, 1)
            If WorksheetFunction.CountIfs(r, r.Cells(i).Value2, r, ">0") > 1 Then
                z = z + 1
            End If
        Next i
        
        If z = 0 Then
            x = vbNullString
            GoTo FillArray
        End If
        
        For i = 1 To UBound(a, 1)
            If WorksheetFunction.CountIfs(r, r.Cells(i).Value2, r, ">0") > 1 Then
                y = WorksheetFunction.Mode(r)
                z = WorksheetFunction.CountIf(r, y)
                For j = 1 To UBound(a, 1)
                    If WorksheetFunction.CountIf(r, r.Cells(j).Value2) = z And r.Cells(j).Value2 <> y Then
                        x = vbNullString
                        GoTo FillArray
                    End If
                Next j
            Else
                x = vbNullString
            End If
        Next i
        x = y
    End If

FillArray:
    For i = LBound(a, 1) To UBound(a, 1)
        a(i, 1) = x
    Next i
    GroupX = a

End Function

UDF test.xlsm
	A	B	C	D
1	Group	B	C	Result
2	A1	A	123
3	A1	#	0
4	A1	A	546
5	A1	#	9
6	A2	A	678	678
7	A2	A	678	678
8	A2	#	5	678
9	A3	A	532	532
10	A3	#	532	532
11	A3	#	0	532
12	A4	#	1000	10
13	A4	A	10	10
14	A5	A	20	20
15	A5	A	35	20
16	A5	A	20	20
17	A6	A	100	5
18	A6	A	5	5
19	A6	A	100	5
20	A6	A	5	5
21	A6	A	5	5
Sheet1

Cell Formulas
Range	Formula
D2:D21	D2	=GroupX()
Dynamic array formulas.

 

[Nicha]

Hi Kevin9999. I've already tested your code. Worked very well for all cases, except for the new group I created 'A7', which has only 2 pairs with equal repetitions.

Group	B	C	Result
A1	A	123
A1	#	0
A1	A	546
A1	#	9
A2	A	678	678
A2	A	678	678
A2	#	5	678
A3	A	532	532
A3	#	0	532
A3	#	0	532
A4	#	1000	10
A4	A	10	10
A5	A	20
A5	A	35
A5	A	20
A6	A	100	5
A6	A	5	5
A6	A	100	5
A6	A	5	5
A6	A	5	5
A7	A	100
A7	A	5
A7	A	100
A7	A	5

I took the liberty of making changes in the code, to only compute the value of the Mode() function for the variable [y], only for the Range [Rng_A] - The range that contains only the 'A + Value' Pairs. But the code fails both on groups 'A5' and 'A7', which result should be respectively (20) and (vbnullstring). Instead, the Mode() function is returning : (Error) on Group 'A5' - witch is good, Mode is working - and returns (100) on Group 'A7' - witch is not good.

I think we need to find a way to perform counts on the Range [Rng_A] values, for (z variable), compare them, and determine:

• If Quantities are equals -> x = vbnullstring
• if a Pair Quantity is greater than the others -> x = y.

What do you think about this?

Function Group_X() As Variant


    'Set Variables
    Dim s As String, r As Range, LRow As Long, i As Long, j As Long, a, x, y, z
    s = Application.Caller.Offset(, -3).Value
    LRow = Columns("A").Find(s, , xlFormulas, , 1, 2).Row
    Set r = Range(Cells(Application.Caller.Row, 3), Cells(LRow, 3))
   
    a = r

    '1. If no "A"s in group
    If WorksheetFunction.CountIf(r.Offset(, -1), "A") = 0 Then
        x = vbNullString
        GoTo FillArray
    End If
   
    '2. If only one "A" in group
    If WorksheetFunction.CountIf(r.Offset(, -1), "A") = 1 Then
        For i = 1 To UBound(a, 1)
            If r.Cells(i).Offset(, -1) = "A" Then
                x = r.Cells(i).Value2
                GoTo FillArray
            End If
        Next i
    End If
   
    '3. If more than one "A" in group
    If WorksheetFunction.CountIfs(r.Offset(, -1), "A", r, ">0") > 1 Then
       
        z = 0
        'Get Range where exists only the pairs with 'A's
        Dim Rng_A As Excel.Range
       
        For i = 1 To UBound(a, 1)
            If WorksheetFunction.CountIfs(r, r.Cells(i).Value2, r, ">0") > 1 Then
                z = z + 1
                If Rng_A Is Nothing Then
                    Set Rng_A = r.Cells(i)
                Else
                    Set Rng_A = Union(Rng_A, r.Cells(i))
                End If
            End If
        Next i
       
        If z = 0 Then
            x = vbNullString
            GoTo FillArray
        End If
       
        'IsError() below not working
        'If IsError(WorksheetFunction.Mode(Rng_A)) Then Stop
        On Error Resume Next
        y = WorksheetFunction.Mode(Rng_A)
        If Err.Number <> 0 Then
            x = vbNullString
            Err.Clear
        Else
            'Most Frequent Value Count
            z = WorksheetFunction.CountIf(Rng_A, y)
            x = y
        End If
           
    End If
       
       
'        For i = 1 To UBound(a, 1)
'            If WorksheetFunction.CountIfs(r, r.Cells(i).Value2, r, ">0") > 1 Then
'                'Valor mais Frequente
'                y = WorksheetFunction.Mode(Rng_A)
'                'Contagem do valor mais Frequente
'                z = WorksheetFunction.CountIf(Rng_A, y)
'                For j = 1 To UBound(a, 1)
'                    If WorksheetFunction.CountIf(r, r.Cells(j).Value2) = z And r.Cells(j).Value2 <> y Then
'                        x = vbNullString
'                        GoTo FillArray
'                    End If
'                Next j
'            Else
'                x = vbNullString
'            End If
'        Next i
'        x = y
'    End If

FillArray:
   
    For i = LBound(a, 1) To UBound(a, 1)
        a(i, 1) = x
    Next i
    GroupX = a


End Function

 

[Nicha]

Hi Kevin9999
I seems that I could reach into a solution for the problem, using your Ideas. I'm testing and I'll give a feed back tomorrow.
My best regards

 

[kevin9999]

I seems that I could reach into a solution for the problem

That's good to hear, because when I added an A7 group as you did in post #18, and tested it with the code I provided in post #17, this is the result I got:

UDF test.xlsm
	A	B	C	D
1	Group	B	C	Result
2	A1	A	123
3	A1	#	0
4	A1	A	546
5	A1	#	9
6	A2	A	678	678
7	A2	A	678	678
8	A2	#	5	678
9	A3	A	532	532
10	A3	#	532	532
11	A3	#	0	532
12	A4	#	1000	10
13	A4	A	10	10
14	A5	A	20	20
15	A5	A	35	20
16	A5	A	20	20
17	A6	A	100	5
18	A6	A	5	5
19	A6	A	100	5
20	A6	A	5	5
21	A6	A	5	5
22	A7	A	100
23	A7	A	5
24	A7	A	100
25	A7	A	5
Sheet1

Cell Formulas
Range	Formula
D2:D25	D2	=GroupX()
Dynamic array formulas.

If that isn't the exact result you've been seeking all along, then I no longer understand your requirements. I've gone as far as I can with this now. You made significant changes to the code I provided in post #17, and the onus is on you to fix any issues that arise from the amended code. I wish you the best of luck with your ongoing development of this project.
Cheers