Count with criteria

[kumara_faith]

Hi,

I have the following table:

Book1
	A	B
1	Interactions	Policy Count
2	Customer came to make a complaint and make enquiry on policy 0012455 and A445587.	2
3	Policyholder made enquiry on policy 9988567 and S4444447.	2
4	Policyholder made enquiry on policy 9988567 and S4444447 and 5454545A	3
5	Policyholder made enquiry on policy 9988567/S4444447/5454545A	3
6	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2
Sheet1

In column A, I have the interactions with customer which also indicates the policy numbers of the customers. Since this interactions are entered manually by the service agent, there could be several possible variations as indicated in column A. Example in row no 6 is not a typo error but it is meant to show an example of the service agent entering the policynumber and the word "and" together as this could also be a possible variation.

In column B , I am trying to count the number of policy numbers mentioned in column A. The correct results are indicated in column B.

Is there a way to use a formula to derive this ? Appreciate all the help.

 

[kumara_faith]

Hi Sanjeev,

Thank you for your solution and patience. That worked. Appreciate it and have a great day ahead.

 

[Peter_SSs]

That worked.

Are you sure?

Example in row no 6 is not a typo error but it is meant to show an example of the service agent entering the policynumber and the word "and" together

What if the service agent missed the space after the "and" or both before and after the "and" as in rows 3 and 4 below. Doesn't the suggested formula return incorrect results as shown in column B?

Another approach might be to employ a user-defined function like this. I have assumed that a policy number contains just one string of consecutive digits of at least 3 characters. This can be altered in the RX.Pattern line of code.
The results of the UDF are shown in column C

Function CountPolicies(s As String) As Long
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\d{3,}"
  CountPolicies = RX.Execute(s).Count
End Function

kumara_faith.xlsm
	A	B	C
1	Interactions	Policy Count	Policy Count
2	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2	2
3	Customer came to make a complaint and make enquiry on policy 00124774 andS449987.	1	2
4	Customer came to make a complaint and make enquiry on policy 00124774andS449987.	1	2
Policies

Cell Formulas
Range	Formula
B2:B4	B2	=SUM(ISNUMBER(--(MID(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,".",""),"/"," ")," ",,1),3,1)))*1)
C2:C4	C2	=CountPolicies(A2)

One other related question: Is it possible that a cell could contain the same policy number more than once?
Example: "Customer queried policy S449987 but was assured policy S449987 is correct"
If this is possible should the count be 1 or 2?

 

[kumara_faith]

Hi Peter,

What if the service agent missed the space after the "and" or both before and after the "and" as in rows 3 and 4 below. Doesn't the suggested formula return incorrect results as shown in column B?
Yes, the scenario above is possible.

One other related question: Is it possible that a cell could contain the same policy number more than once?
Example: "Customer queried policy S449987 but was assured policy S449987 is correct"
If this is possible should the count be 1 or 2?

Yes, the above scenario is also possible. In the above, the correct count should be 1.

 

[Peter_SSs]

Yes, the above scenario is also possible. In the above, the correct count should be 1.

Then we would need a clear definition of exactly what a policy number can look like in terms of how many letters and/or numbers can make up a policy number, whether the letters can be upper and/or lower case, where any letters might occur in the policy number, whether any characters other than letters/numbers can exist in a policy number, etc.

From your examples in post 9 it seems the following combinations may be possible

1. Exactly 7 digits with no other characters
2. 1 upper case letter followed by 6 digits
3. 1 upper case letter followed by 7 digits
4. 7 digits followed by 1 upper case letter
5. Exactly 8 digits with no other characters
6. 1 lower case letter followed by 6 digits
7. 5 digits followed by 1 upper case letter

Are there other possibilities or any of the list above incorrect?

Can you confirm that a vba user-defined function similar to the one in post 12 would be acceptable?

 

[kumara_faith]

Hi Peter,

The policy number will be at least 3 digits or more with any of the letter possibilities you have mentioned above. A formula solution is preferable as my organization has actually blocked all macro due to security reasons. However, if a formula solution is not possible, I will try to get in touch with my tech security to explore the exception approval process.

 

[Peter_SSs]

The policy number will be at least 3 digits or more with any of the letter possibilities you have mentioned above.

Hmm, does that means a policy number could be 35 digits?

"Customer queried policy 449987 andd449987 was clarified"
Using logic only, it would be difficult to determine if the above example
- had two policy numbers (449987 and d449987) and the agent accidentally missed a space after "and", or
- had just one policy number (449987) and the agent accidentally double typed the d and missed a space.

I think any fool proof count will be difficult and I think any attempt would not be feasible without vba though I am happy to be proved wrong.

 

[kumara_faith]

Hi Peter,

Sorry. To clarify. the policy number will be at least 3 digits minimum and maximum 8 digits with any of the letter combinations mentioned earlier.

 

[Peter_SSs]

I think any fool proof count will be difficult ...

... as evidenced by row 6 below. There is no logical way to know if the second policy number is 449987 (same as the first policy number which would result in a count of 1) or if it is d449987 (being a different policy number which results in a count of 2)

Function CountPolicies(s As String) As Long
  Dim RX As Object, Mtch As Object, d As Object

  Set d = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|\D)(([A-Za-z]\d{3,8})|(\d{3,8}[A-Z])|(\d{3,8}))(?=\D|$)"
  For Each Mtch In RX.Execute(s)
    d(Mtch.SubMatches(1)) = 1
  Next Mtch
  CountPolicies = d.Count
End Function

kumara_faith.xlsm
	A	B
1	Interactions	Policy Count
2	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2
3	Customer came to make a complaint and make enquiry on policy 00124774 andS449987.	2
4	Customer came to make a complaint and make enquiry on policy 00124774andS449987.	2
5	Customer queried policy S449987 but was assured policy S449987 is correct	1
6	Customer queried policy 449987 and449987 was clarified	2
7	Customer came to make a complaint and make enquiry on policy 0012455 and A445587.	2
8	Policyholder made enquiry on policy 9988567 and S4444447.	2
9	Policyholder made enquiry on policy 9988567 and S4444447 and 5454545A	3
10	Policyholder made enquiry on policy 9988567/S4444447/5454545A	3
11	Customer came for surrender. Managed to conserve and make PW. 4545445A/5454545A	2
12	Customer came for 3 policy to surrender. Managed to conserve 1 policy and another 2 PW	0
13	Customer came to surrender 3. Managed to conserve 1 policy and another 2 PW	0
14	Customer came to surrender 3. Managed to conserve 1 policy and another 2 PW/a444557/45545A	2
15	Customer came to surrender 3. Managed to conserve 1 and another 2 PW/a444557/45545A	2
Policies

Cell Formulas
Range	Formula
B2:B15	B2	=CountPolicies(A2)

 

[kumara_faith]

Hi Peter,

Thank you for your solution and patience. Will try out the VBA solution.

 

[Peter_SSs]

You're welcome. Hope it works well enough for you. Thanks for the follow-up.