checking for missing values
 

Hi all,

Is there a way to check a range for particular values that are
missing?
Suppose I have a list of possible values: A,1,F,G,K,T,3,W,X
And I have the following values in column C:

A
K
1
A
T
X
X
G

Ideally, I'd need a function that would return the following string:

"Missing items: F, 3, W"

The actual list has 20 possible values. My guess is that one should
work with arrays, but my experience with those is very limited. Could
this be solved with some VBA maybe?

thanks for your help,
Jerry

checking for missing values
 

Jerry,

Kludgy, but ...

="Missing
items"&(IF(COUNTIF(A:A,1)=0,",1",""))&(IF(COUNTIF( A:A,3)=0,",3",""))&(IF(COU
NTIF(A:A,"A")=0,",A",""))&(IF(COUNTIF(A:A,"F")=0," ,F",""))&(IF(COUNTIF(A:A,"
G")=0,",G",""))&(IF(COUNTIF(A:A,"K")=0,",K",""))&( IF(COUNTIF(A:A,"T")=0,",T"
,""))&(IF(COUNTIF(A:A,"W")=0,",W",""))&(IF(COUNTIF (A:A,"X")=0,",X",""))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jerry" wrote in message
...
Hi all,

Is there a way to check a range for particular values that are
missing?
Suppose I have a list of possible values: A,1,F,G,K,T,3,W,X
And I have the following values in column C:

A
K
1
A
T
X
X
G

Ideally, I'd need a function that would return the following string:

"Missing items: F, 3, W"

The actual list has 20 possible values. My guess is that one should
work with arrays, but my experience with those is very limited. Could
this be solved with some VBA maybe?

thanks for your help,
Jerry

checking for missing values
 

Jerry,

Here is a programming solution to go along with Bob's formula...

'-------------------------------------------------------------
Sub FindWhatIsMissing()
On Error GoTo ErrHandler
Dim colCharacters As Collection
Dim strCharacters As String
Dim arrArray As Variant
Dim objRange As Range
Dim objCell As Range
Dim lngNum As Long

Set objRange = Range("D11:D30")
Set colCharacters = New Collection
'Fill array with the characters you are looking for - replace as necessary
arrArray = Array("A", "B", "C", "D", "E", "F", "19", "H", _
"J", "K", "3", "M", "N", "6", "R", "S", _
"T", "X", "Y", "Z")

'Fill collection object with the values from the 20 cell range
For Each objCell In objRange
colCharacters.Add vbNullString, UCase$(objCell.Value)
Next 'objCell

'Try to add values from the array to the collection
'Error occurs if a duplicate value
For lngNum = 0 To 19
On Error Resume Next
colCharacters.Add vbNullString, arrArray(lngNum)
If Err.Number = 0 Then
'Add missing character to end of String variable
strCharacters = strCharacters & arrArray(lngNum) & ", "
Else
Err.Clear
End If
Next 'lngNum
On Error GoTo ErrHandler

Application.Cursor = xlDefault
If Len(strCharacters) Then
MsgBox "Missing characters a " & vbNewLine & strCharacters, , " Jerry Did
It"
Else
MsgBox "No characters missing", , " Jerry Did It"
End If

CleanUp:
Set colCharacters = Nothing
Set objCell = Nothing
Set objRange = Nothing
Exit Sub

ErrHandler:
Beep
GoTo CleanUp
End Sub
'--------------------------------------------

Regards,
Jim Cone
San Francisco, CA

"Jerry" wrote in message
...
Hi all,
Is there a way to check a range for particular values that are
missing?
Suppose I have a list of possible values: A,1,F,G,K,T,3,W,X
And I have the following values in column C:
A
K
1
A
T
X
X
G
Ideally, I'd need a function that would return the following string:

"Missing items: F, 3, W"
The actual list has 20 possible values. My guess is that one should
work with arrays, but my experience with those is very limited. Could
this be solved with some VBA maybe?
thanks for your help,
Jerry

checking for missing values
 

Jerry

A UDF that may help.

Tony

Function checker(x As Range)
arr = Array("A", 1, "F", "G", "K", "T", 3, "W", "X")
Dim chkitems As Variant
chkitems = x.Value
checker = "Missing Items: "
For Each ce In arr
On Error GoTo errone
fred = WorksheetFunction.Match(ce, chkitems, 0)
Next ce

errone:
Select Case Err.Number
Case 1004
checker = checker & ce & ", "
End Select

Resume Next

End Function


----- Jerry wrote: -----

Hi all,

Is there a way to check a range for particular values that are
missing?
Suppose I have a list of possible values: A,1,F,G,K,T,3,W,X
And I have the following values in column C:

A
K
1
A
T
X
X
G

Ideally, I'd need a function that would return the following string:

"Missing items: F, 3, W"

The actual list has 20 possible values. My guess is that one should
work with arrays, but my experience with those is very limited. Could
this be solved with some VBA maybe?

thanks for your help,
Jerry

checking for missing values
 

Guys, thanks for all your suggestions. I tried all solutions, and all
worked as requested. Although this isn't some coding contest, my vote
goes to Tony's suggestion.

cheers,
Jerry

acw wrote in message ...
Jerry

A UDF that may help.

Tony

Function checker(x As Range)
arr = Array("A", 1, "F", "G", "K", "T", 3, "W", "X")
Dim chkitems As Variant
chkitems = x.Value
checker = "Missing Items: "
For Each ce In arr
On Error GoTo errone
fred = WorksheetFunction.Match(ce, chkitems, 0)
Next ce

errone:
Select Case Err.Number
Case 1004
checker = checker & ce & ", "
End Select

Resume Next

End Function


----- Jerry wrote: -----

Hi all,

Is there a way to check a range for particular values that are
missing?
Suppose I have a list of possible values: A,1,F,G,K,T,3,W,X
And I have the following values in column C:

A
K
1
A
T
X
X
G

Ideally, I'd need a function that would return the following string:

"Missing items: F, 3, W"

The actual list has 20 possible values. My guess is that one should
work with arrays, but my experience with those is very limited. Could
this be solved with some VBA maybe?

thanks for your help,
Jerry