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?

 

[kevin9999]

I'm sure there's a more elegant way than this, but ugly as it is, it does seem to give you what you want. Assumes your data starts in column A row 2, code run with the sheet active.

Option Explicit
Sub A_Groups()
    Dim a, b, i As Long, j As Long, k As Long, m As Long, s As String, x As String
    
    a = Range("A2:D" & Cells(Rows.Count, "C").End(xlUp).Row)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        If a(i, 2) = "A" And a(i, 3) > 0 Then b(i, 1) = a(i, 1) & "-" & a(i, 3)
    Next i
    
    b = WorksheetFunction.Sort(WorksheetFunction.Unique(b))
    For i = 1 To UBound(b, 1) - 1
        s = Left(CStr(b(i, 1)), 2)
        For j = 1 To UBound(b, 1)
            If Left(CStr(b(j, 1)), 2) = s Then k = k + 1
        Next j
        If k = 1 Then
            x = Mid(CStr(b(i, 1)), 4, Len(CStr(b(i, 1))) - 3)
            For m = 1 To UBound(a, 1)
                If Left(CStr(a(m, 1)), 2) = s Then
                    a(m, 4) = x
                End If
            Next m
        End If
        k = 0
    Next i
    Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Before:

A Groups.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
7	A2	A	678
8	A2	#	5
9	A3	A	532
10	A3	#	0
11	A3	#	0
Sheet1

After:

A Groups.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
Sheet1

 

[Nicha]

I'm sure there's a more elegant way than this, but ugly as it is, it does seem to give you what you want. Assumes your data starts in column A row 2, code run with the sheet active.

Option Explicit
Sub A_Groups()
    Dim a, b, i As Long, j As Long, k As Long, m As Long, s As String, x As String
   
    a = Range("A2:D" & Cells(Rows.Count, "C").End(xlUp).Row)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        If a(i, 2) = "A" And a(i, 3) > 0 Then b(i, 1) = a(i, 1) & "-" & a(i, 3)
    Next i
   
    b = WorksheetFunction.Sort(WorksheetFunction.Unique(b))
    For i = 1 To UBound(b, 1) - 1
        s = Left(CStr(b(i, 1)), 2)
        For j = 1 To UBound(b, 1)
            If Left(CStr(b(j, 1)), 2) = s Then k = k + 1
        Next j
        If k = 1 Then
            x = Mid(CStr(b(i, 1)), 4, Len(CStr(b(i, 1))) - 3)
            For m = 1 To UBound(a, 1)
                If Left(CStr(a(m, 1)), 2) = s Then
                    a(m, 4) = x
                End If
            Next m
        End If
        k = 0
    Next i
    Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Before:

A Groups.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
7	A2	A	678
8	A2	#	5
9	A3	A	532
10	A3	#	0
11	A3	#	0
Sheet1

After:

A Groups.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
Sheet1

Thank you Kevin9999. The result is perfect. I was thinking more in a function that is called at the first row of each group, and return the value to fill column D.
For example: my process calling your function at row 6, for group A2, and return the value 678.
At the beginning of row 9, my process calls your function, and receives the value 532 from your function, and my process fills column D with that value, and so on...

 

[kevin9999]

I was thinking more in a function that is called at the first row of each group

Is this more like what you were looking for?

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
    
    If WorksheetFunction.MinIfs(r, r.Offset(, -1), "A", r, ">0") = WorksheetFunction.MaxIfs(r, r.Offset(, -1), "A", r, ">0") Then
        x = WorksheetFunction.MaxIfs(r, r.Offset(, -1), "A", r, ">0")
    Else
        x = vbNullString
    End If
    
    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
Sheet1

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

 

[Nicha]

Is this more like what you were looking for?

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
   
    If WorksheetFunction.MinIfs(r, r.Offset(, -1), "A", r, ">0") = WorksheetFunction.MaxIfs(r, r.Offset(, -1), "A", r, ">0") Then
        x = WorksheetFunction.MaxIfs(r, r.Offset(, -1), "A", r, ">0")
    Else
        x = vbNullString
    End If
   
    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
Sheet1

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

My best regards Kevin9999.
Yes! This was what I looking for. Thank you for sharing with us.

 

[kevin9999]

My best regards Kevin9999.
Yes! This was what I looking for. Thank you for sharing with us.

Glad I could help, and thanks for the feedback

 

[Nicha]

Glad I could help, and thanks for the feedback

Hello Kevin9999
I'm testing your code and I'm not being able to make it run properly. I need a favor, if you don't mind, please.
Can you comment the process, including Variables? I'm not able to understand what is happening during execution. And when I try to run step by step he seems to jump out in the line below, presenting a [#Value] on D2:

If WorksheetFunction.MinIfs(r, r.Offset(, -1), "A", r, ">0") = WorksheetFunction.MaxIfs(r, r.Offset(, -1), "A", r, ">0") Then

This code is too complex for me, so I'm very sorry that I'm causing you trouble.
My excel Version is 2016.

Can you help me please?

 

[Nicha]

Hello Kevin9999
I'm testing your code and I'm not being able to make it run properly. I need a favor, if you don't mind, please.
Can you comment the process, including Variables? I'm not able to understand what is happening during execution. And when I try to run step by step he seems to jump out in the line below, presenting a [#Value] on D2:

If WorksheetFunction.MinIfs(r, r.Offset(, -1), "A", r, ">0") = WorksheetFunction.MaxIfs(r, r.Offset(, -1), "A", r, ">0") Then

This code is too complex for me, so I'm very sorry that I'm causing you trouble.
My excel Version is 2016.

Can you help me please?

If we'll insert the Group 'A4', do your code still work?

Group	B	C	Result
A1	A	123	#VALUE!
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

 

[Nicha]

I'll try to be more especific. Maby I'm not explaining myself very well, and for that I need you to forgive me. Please

Considering the table below:

Group	B	C	Result
A1	A	123	#VALUE!
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

• In Group 'A1' we have two occurrences with 'A' - 'A123' and 'A546' - but they both have different values. So we can't choose between them.
• In Group 'A2' we have two occurrences with 'A' - 'A678'. When we have two or more occurrences with the same value the result can be set to the correspondent value.
• In Group 'A3' we have just one Occurrence with 'A' - 'A532'. When we have just one ocurrence with 'A' the value is the correspondent one. The same passes with Group 'A4'
• In the Group 'A5' we have three occurrences with 'A' - 'A20', 'A30' and another 'A20'. When we have two or more occurrences we choose the one that has the high Frequency.

Can you still help please?

 

[kevin9999]

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.