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]

Hi Kevin9999. Sorry for the inconvenience.
The code is working right, but I'm having trouble with the instruction that count's if the values in Column [C] are ">0". Thats because in real table the column [C] is Text.

    '3. If more than one "A" in group
    If WorksheetFunction.CountIfs(r.Offset(, -1), "A", r, ">0") > 1 Then

So, theres a solution To Count if ">0" in a column with numbers formated as text; or count if those text values are numeric?

Pseudo code:

    '3. If more than one "A" in group
    If WorksheetFunction.CountIfs(r.Offset(, -1), "A", r, "isNumeric") > 1 Then
Or
    '3. If more than one "A" in group
    If WorksheetFunction.CountIfs(r.Offset(, -1), "A", clng(r), ">0") > 1 Then

Thank's very, very mutch. The rest it's perfectly ok.

p.s. I can't change the format of column [C].

 

[kevin9999]

Thats because in real table the column [C] is Text.

This is the first time you've mentioned this, and it does make a significant difference. I don't think the UDF approach will work now. You may have to go back to some kind of sub routine to (perhaps) convert column C to numeric - and then back again to text at the conclusion of the code? Just a suggestion - post a new thread with your existing UDF & ask if anyone can resolve the issue of column C being text not numeric. There's better brains than me on here that might have an elegant solution.

 

[Nicha]

My best regards to you, for all your help and time disposal.