Fuction for Quality Check

[StudentofExcel]

Hello All,

If some come can point me to right resources I would appreciate it.

My problem is that I would like to create function =QC() that returns "Good" or "Bad".

And I would use it to check all "code numbers" if they have less then 6 character to return text "Bad" next to a cell. If first two caracthers are not: "10,20,30,40,50,60,70,80,90" it should return "Bad", if next two caracthers (3rd & 4th) are not: "AA,BB,CC,DD,EE,FF,GG,RR" return "Bad", if next two caracthers (5th & 6th) are not: "11,22,33,44,55,66,77,88,99" return bad, if "Code number" is "Good" but it does contain anywhere in code number: "Err","Error","EER" it should return "Bad".

Example

A (Code numbers) B

10CC22 Good
10CC22Err Bad
11CC22 Bad
30RR44 Good
30RR4 Bad

The function would be located in B2 and it would be based on A2, I would drag it to the end of a column of B. =QC(A2)
If someone could help I would very appreciate it.

Best regards,

Jenan

 

[Rick Rothstein]

Can you clarify something for me regarding the following conditions...

if next two caracthers (3rd & 4th) are not: "AA,BB,CC,DD,EE,FF,GG,RR" return "Bad"

1) Do those letters have to be upper case only or would "aa", "bb", etc. be alright (in other words, does letter case matter for the 3rd and 4th characters)?

if "Code number" is "Good" but it does contain anywhere in code number: "Err","Error","EER" it should return "Bad".

2) Can any other text appear after the 6th character except for those specific three words?

3) What if "ERR" or "ERROR" or "eer" appear after the 6th character (in other words, does letter case matter after the 6th character)?

 

[StudentofExcel]

Thank you for your reply.

All letters should be in upper case if not then "Bad".

1. It does matter for first two characters it should be all uppercase "<g class="gr_ gr_37 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-del replaceWithoutSep" id="37" style="background-position: -1px calc(100% + 3px); color: rgb(34, 34, 34); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: arial, sans-serif; font-size: small; font-style: normal; font-weight: 400; word-spacing: 0px; border-bottom-color: transparent; border-bottom-width: 2px; border-bottom-style: solid; display: inline; white-space: normal; orphans: 2; widows: 2; background-image: url("data:image/svg+xml;charset=utf8,%3Csvg xmlns='http://www.w3.org/2000/svg' width='100%' height='100%'%3E%3Cline opacity='0.75' x1='4' y1='100%' x2='100%' y2='100%' transform='translate(-1.5, -2.5)' stroke-width='3' stroke-linecap='round' stroke='%23fc5454'/%3E%3C/svg%3E"); background-repeat: no-repeat; background-size: calc(100% + 1px) 100%; background-color: rgb(255, 255, 255); font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial; animation: gr__appear_critical 0.4s ease forwards;" data-gr-id="37">Good",</g> if they are not then "Bad",
2. It can for Example "30RR44-W" this is "Good" but "30RR44-Err" this is "Bad" also this is Bad "30RR44-W-ERR",
3. It also matters after 6th characters for "ERR", "ER", "ERROR" to be all in upper case because of "30RR44-W" = Good.

I hope my answer helped.

Thank you for your time

 

[Fennek]

Hi,

as a startpoint:

function QC(rng as range) as boolean
with rng
if left(.value, 2)*1 mod 10 <> 10 then goto poor
if instr(1, "AABBCCDDEEFFGGRR", mid(.value3,2)) = 0 then goto poor
dd = mid(.value,5,2)*1
if not(isnumeric(dd) and left(dd,1) = right(dd,1)) then goto poor
if instr(1,.value, "err") > 0 or instr(1,.value, "eer") > 0 then goto poor

QC = true :exit function
poor:
end with
end function

regards

 

[Rick Rothstein]

Give this function a try...

[table="width: 500"]
[tr]
	[td]Function QC(S As String) As String
  QC = "Bad"
  If S Like "[1-9]0[A-GR][A-GR][1-9][1-9]*" And Mid(S, 3, 1) = Mid(S, 4, 1) And Mid(S, 5, 1) = Mid(S, 6, 1) Then
    If InStr(1, S, "Err", vbTextCompare) = 0 And InStr(1, S, "Error", vbTextCompare) = 0 And InStr(1, S, "EER", vbTextCompare) = 0 Then QC = "Good"
  End If
End Function[/td]
[/tr]
[/table]

 

[Ken Cowen]

Try something like this. You may need to tweak some of the logic, but it works for me based on my assumptions about what you want to do.

Option Explicit


Function QC(d)


Dim test As Boolean
Dim test1 As Boolean, test2 As Boolean, test3 As Boolean
Dim str As String

test = True

test1 = True
test2 = True
test3 = True

d = CStr(d)

QC = "Bad"

If Len(d) < 6 Then test = False

str = Mid(d, 1, 2)
If Application.IsError(Application.Match(str, Array("10", "20", "30", "40", "50", "60", "70", "80", "90"), False)) Then test = False

str = Mid(d, 3, 2)
If Application.IsError(Application.Match(str, Array("AA", "BB", "CC", "DD", "EE", "FF", "GG", "RR", ""), False)) Then test = False

str = Mid(d, 5, 2)
If Application.IsError(Application.Match(str, Array("11", "22", "33", "44", "55", "66", "77", "88", "99"), False)) Then test = False

test1 = (Len(d) = Len(Application.Substitute(d, "Err", "")))
test2 = (Len(d) = Len(Application.Substitute(d, "Error", "")))
test3 = (Len(d) = Len(Application.Substitute(d, "EER", "")))

If Application.And(test1, test2, test3) Then test = False

If test Then QC = "Good"

End Function

I hope this helps.

Ken

 

[Ken Cowen]

After looking over the compact answers that showed up while i was working on this, I realized I misread the error stuff. The function I posted returns Bad unless one of the Err words is present. Upon further review it seems that those strings trigger a "Bad" response, which seems more logical. So, the following should be better,

Function QC(d)


Dim test As Boolean
Dim test1 As Boolean, test2 As Boolean, test3 As Boolean
Dim str As String

test = True

test1 = True
test2 = True
test3 = True

d = CStr(d)

QC = "Bad"

If Len(d) < 6 Then test = False

str = Mid(d, 1, 2)
If Application.IsError(Application.Match(str, Array("10", "20", "30", "40", "50", "60", "70", "80", "90"), False)) Then test = False

str = Mid(d, 3, 2)
If Application.IsError(Application.Match(str, Array("AA", "BB", "CC", "DD", "EE", "FF", "GG", "RR", ""), False)) Then test = False

str = Mid(d, 5, 2)
If Application.IsError(Application.Match(str, Array("11", "22", "33", "44", "55", "66", "77", "88", "99"), False)) Then test = False

test1 = (Len(d) <> Len(Application.Substitute(d, "Err", "")))
test2 = (Len(d) <> Len(Application.Substitute(d, "Error", "")))
test3 = (Len(d) <> Len(Application.Substitute(d, "EER", "")))

If Application.Or(test1, test2, test3) Then test = False

If test Then QC = "Good"

End Function

 

[StudentofExcel]

This seems to work Could you just advise me how to add future letters or numbers to this function.

Thank you very much

 

[StudentofExcel]

I have tested this and it returnes everything false.

 

[StudentofExcel]

Rick Rothstein

This seems to work Could you just advise me how to add future letters or numbers to this function.

Thank you very much

__
Fennek

I have tested this and it returnes everything false.
__

Ken Cowen

I have tested it and it works, it's maybe better for me to use this function because I could understand it and I can add more "Codes".

Thank you all.