using array, Match, trying to eliminate duplicates -> how to return or translate result to a boolean?

I am cycling through some larger multidimensional arrays to find records
that match certain criteria. When I find a match, I copy a row of data to a
smaller array, which ultimately populates a row in my spreadsheet (after I
find all the matches, I add them to the target cell with a chr(10) between
each, to put each match on a new "row" within the cell).

I've run into a problem with duplicate matches, which results in my row
having duplicate data in it. I think I know how to address this, I'm just
not sure of the syntax to make it work.

Assuming I will never have more than 10 good (non-duplicate) matches, and I
do have a unique field to compare, I was thinking of adding a new
one-dimensional array and clearing it between excel rows. Each time I find
an entry that matches my criteria, I would just use the match function to
see if that value was already in the one dimensional array, and if so, skip
to the next match (if it isn't in the 1-D array, add it as normal).

So I've put some code below, but it returns a position or N/A. What is the
best way to translate that into a boolean so I can use it in my IF
statement?

'--------------------------------------------------------------
Dim CompareArray(1 to 10)
Dim CompareItem as String
Dim FoundDuplicate as Boolean

'do stuff until a match is found

FoundMatch = Excel.worksheetfunction.Match(CompareItem, CompareArray,0)

'how do I translate FoundMatch (integer or N/A) into FoundDuplicate Boolean?

If FoundDuplicate = False then
'add it to my larger data array
End if
'---------------------------------------------------------------


--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

If I read you right then you are creating for each matching row a long
string of each of the array elements that then populates a cell. Have you
considered using a Collection object (look up in vba help), this will throw
an error if you try to add a duplicate item. So just ignore the error.
Then read out the collection directly into your worksheet. Something
like......

Dim YourData As New Collection, YourString As String

' build your string here

On Error Resume Next
YourData.Add YourString, CStr(YourString)

' read out collection
For Each Item In YourData
'populate sheet with values
Next Item
--
Cheers
Nigel



"KR" <[email protected]> wrote in message
news:%23Kz%[email protected]...
> I am cycling through some larger multidimensional arrays to find records
> that match certain criteria. When I find a match, I copy a row of data to
a
> smaller array, which ultimately populates a row in my spreadsheet (after I
> find all the matches, I add them to the target cell with a chr(10) between
> each, to put each match on a new "row" within the cell).
>
> I've run into a problem with duplicate matches, which results in my row
> having duplicate data in it. I think I know how to address this, I'm just
> not sure of the syntax to make it work.
>
> Assuming I will never have more than 10 good (non-duplicate) matches, and
I
> do have a unique field to compare, I was thinking of adding a new
> one-dimensional array and clearing it between excel rows. Each time I find
> an entry that matches my criteria, I would just use the match function to
> see if that value was already in the one dimensional array, and if so,
skip
> to the next match (if it isn't in the 1-D array, add it as normal).
>
> So I've put some code below, but it returns a position or N/A. What is the
> best way to translate that into a boolean so I can use it in my IF
> statement?
>
> '--------------------------------------------------------------
> Dim CompareArray(1 to 10)
> Dim CompareItem as String
> Dim FoundDuplicate as Boolean
>
> 'do stuff until a match is found
>
> FoundMatch = Excel.worksheetfunction.Match(CompareItem, CompareArray,0)
>
> 'how do I translate FoundMatch (integer or N/A) into FoundDuplicate
Boolean?
>
> If FoundDuplicate = False then
> 'add it to my larger data array
> End if
> '---------------------------------------------------------------
>
>
> --
> The enclosed questions or comments are entirely mine and don't represent
the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>

To directly answer your question:

If you're using Excel.WorksheetFunction.Match(), why not examine the result with

Excel.WorksheetFunction.IsNA()

or use one of the VBA Is[ ] functions like IsNumber()?


Colin

Close, but I'm actually building multiple strings (about 30) for each row
(e.g. 30 columns used). I only need to compare one particular field to
determine if I should add the next set of data to those 30 strings or not.
In the collection solution, I'd also need to use the error condition to do
conditionally stuff, so I wouldn't be able to just resume next, and
unfortunately I don't have a conceptual understanding of error trapping and
using that information. One of the many things on my list of "stuff it would
be really useful to know more about".

I don't mind the 'hack' solution of just comparing that small array using
Match, if someone can tell me how to capture the range of N/A to integer
possible outcomes of Match, so I can use it. I'm thinking that I could do
something like

If FoundMatch =N/A then
'go ahead and add all my strings
End if

p.s., since I know I'll be asking in my next post anyway... what is the
proper way to describe "N/A" in VBA, per the first line of the If statement
above? Should it be in quotes? I remember from another post (a long time
ago) that it was something not immediately obvious or intuitive...


"Nigel" <[email protected]> wrote in message
news:[email protected]...
> If I read you right then you are creating for each matching row a long
> string of each of the array elements that then populates a cell. Have you
> considered using a Collection object (look up in vba help), this will
throw
> an error if you try to add a duplicate item. So just ignore the error.
> Then read out the collection directly into your worksheet. Something
> like......
>
> Dim YourData As New Collection, YourString As String
>
> ' build your string here
>
> On Error Resume Next
> YourData.Add YourString, CStr(YourString)
>
> ' read out collection
> For Each Item In YourData
> 'populate sheet with values
> Next Item
> --
> Cheers
> Nigel
>
>
>
> "KR" <[email protected]> wrote in message
> news:%23Kz%[email protected]...
> > I am cycling through some larger multidimensional arrays to find records
> > that match certain criteria. When I find a match, I copy a row of data
to
> a
> > smaller array, which ultimately populates a row in my spreadsheet (after
I
> > find all the matches, I add them to the target cell with a chr(10)
between
> > each, to put each match on a new "row" within the cell).
> >
> > I've run into a problem with duplicate matches, which results in my row
> > having duplicate data in it. I think I know how to address this, I'm
just
> > not sure of the syntax to make it work.
> >
> > Assuming I will never have more than 10 good (non-duplicate) matches,
and
> I
> > do have a unique field to compare, I was thinking of adding a new
> > one-dimensional array and clearing it between excel rows. Each time I
find
> > an entry that matches my criteria, I would just use the match function
to
> > see if that value was already in the one dimensional array, and if so,
> skip
> > to the next match (if it isn't in the 1-D array, add it as normal).
> >
> > So I've put some code below, but it returns a position or N/A. What is
the
> > best way to translate that into a boolean so I can use it in my IF
> > statement?
> >
> > '--------------------------------------------------------------
> > Dim CompareArray(1 to 10)
> > Dim CompareItem as String
> > Dim FoundDuplicate as Boolean
> >
> > 'do stuff until a match is found
> >
> > FoundMatch = Excel.worksheetfunction.Match(CompareItem, CompareArray,0)
> >
> > 'how do I translate FoundMatch (integer or N/A) into FoundDuplicate
> Boolean?
> >
> > If FoundDuplicate = False then
> > 'add it to my larger data array
> > End if
> > '---------------------------------------------------------------
> >
> >
> > --
> > The enclosed questions or comments are entirely mine and don't represent
> the
> > thoughts, views, or policy of my employer. Any errors or omissions are
my
> > own.
> >
> >
>
>

"KR" wrote:

> If FoundMatch =N/A then
> 'go ahead and add all my strings
> End if
>
> p.s., since I know I'll be asking in my next post anyway... what is the
> proper way to describe "N/A" in VBA, per the first line of the If statement
> above? Should it be in quotes? I remember from another post (a long time
> ago) that it was something not immediately obvious or intuitive...
>

KR,
"=NA()" is the way #NA is defined in a cell. You could try the following
methodolgy. I tested it with =NA(), letters, and numbers in the cells that
are inserted into the DataArray and the CriteriaArray. The methodolgy did
not work if .Cells( i, DataCol).Value was used instead of .Cells( i,
DataCol).Text
..
..
Dim NumDataRows As Long, NumCriRows As Long
Dim DataArray() As String
Dim CriteriaArray() As String
Dim CountArray() As Single
Dim ThisSheet As String

ThisSheet = ActiveSheet.Name
Worksheets(ThisSheet).Activate

For i = 1 To NumDataRows
DataArray(i) = Worksheets(ThisSheet).Cells( i, DataCol).Text
Next i

For j = 1 To NumCriRows
CriteriaArray(j) = Worksheets(ThisSheet).Cells( j, CriCol).Text
Next j

For j = 1 To NumCriRows
cntr = 0
For i = 1 To NumDataRows
If (DataArray(i) = CriteriaArray(j)) Then
cntr = cntr + 1
' whatever else you want to do.......
End If
Next i
CountArray(j, 1) = cntr
Next j
..
..

HTH,
Raul

Thank you to all who contributed. The final solution I adopted (in case
anyone was watching this thread) was to create a small 1-dimensional array
and use Application.Match (exact match) to see if each successive key was
already in the new array or not. I used IsError on the return value from the
Application.Match to handle the NA() value that is returned if the item is
not found in the array. If it was an error [NA()], then I ran the code to
add my key to the small array, and then captured the rest of the info from
my larger array that corresponded to that value. Clean and simple, much
better than what I started with. Thanks again,
Keith