Can I do this with arrays?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I'd like to compare one array to another and remove any values from the
first that are the same as values in the second. So if the first array is
(12), (13), (14), (21), (22), (23),
and the second is (12), (13), (14),
is there an array formula that would reduce the first array to (21), (22),
(23).

If not, I suppose I can simply iterate through the second and grab a value,
then iterate through the first and delete any matching value.

Of course, this would require a ReDim on the first array, right? And to do
that, I would need to know how many values I now have in the first array,
right? But did the action of the deleting actually remove the data
positions from the first array and change the UBound from 6 to 3, or did it
just fill those data positions with 0 or null? So after the deletion, is my
first array really (21), (22), (23)? Or is it (), (), (), (21), (22),
(23)? If the latter, how do I remove the nulls?

Any help is greatly appreciated.
Ed
 
It would only clear or null the value, it would not change the dimension of
the array. After you clear all the values, you would need to loop through
the array and move the values to be contiguous, then redim preserve the
array to reduce its size.
 
Ed,

See the example below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim Arr1 As Variant
Dim Arr2 As Variant
Dim i As Integer
Dim j As Integer
Dim myCount As Integer
Dim myMsg As String

'Arry of values to remove
Arr1 = Array(12, 13, 14)
'Array of values to clear out
Arr2 = Array(12, 13, 14, 21, 22, 23)

On Error Resume Next
For i = 1 To UBound(Arr1)
Arr2(Application.Match(Arr1(i), Arr2, False)) = ""
Next i

myCount = UBound(Arr2)

For i = UBound(Arr2) To 1 Step -1
If Arr2(i) = "" Then
myCount = myCount - 1
For j = i To UBound(Arr2) - 1
Arr2(j) = Arr2(j + 1)
Next j
End If
Next i

ReDim Preserve Arr2(1 To myCount)

For i = 1 To UBound(Arr2)
myMsg = myMsg & Arr2(i) & ", "
Next i

MsgBox "The remaining values are: " & Left(myMsg, Len(myMsg) - 2)

End Sub
 
Beautiful, Bernie! Thank you!!
Ed

Bernie Deitrick said:
Ed,

See the example below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim Arr1 As Variant
Dim Arr2 As Variant
Dim i As Integer
Dim j As Integer
Dim myCount As Integer
Dim myMsg As String

'Arry of values to remove
Arr1 = Array(12, 13, 14)
'Array of values to clear out
Arr2 = Array(12, 13, 14, 21, 22, 23)

On Error Resume Next
For i = 1 To UBound(Arr1)
Arr2(Application.Match(Arr1(i), Arr2, False)) = ""
Next i

myCount = UBound(Arr2)

For i = UBound(Arr2) To 1 Step -1
If Arr2(i) = "" Then
myCount = myCount - 1
For j = i To UBound(Arr2) - 1
Arr2(j) = Arr2(j + 1)
Next j
End If
Next i

ReDim Preserve Arr2(1 To myCount)

For i = 1 To UBound(Arr2)
myMsg = myMsg & Arr2(i) & ", "
Next i

MsgBox "The remaining values are: " & Left(myMsg, Len(myMsg) - 2)

End Sub
Click to expand...
 
Beautiful, Bernie! Thank you!!

You're welcome.

I forgot to mention that the code, as written, requires

Option Base 1

at the top of the module.

HTH,
Bernie
MS Excel MVP
 
I put the Option Base 1 in as a matter of course - it's too much extra work
for me to remember to subtract 1! <g>

I did notice that the revised array (Arr2) is still as large as before -
that is, UBound before and after are the same. Going from Tom's response,
I'm assuming this is because some value positions are now "" or null, but
are still there. I must have missed something in your code when I
transposed it into mine. If I find it, I'll let you know.

Ed
 
Ed,

Add this as the last line of the macro:

MsgBox "Array 2 size is now: " & UBound(Arr2)

With your example, I get 3....

HTH,
Bernie
MS Excel MVP
 
Never mind! When I dropped this into the macro I was using to test my
"trials and errors", I reassigned all the variables in your code to match my
code - except
myCount = myCount - 1
so when I reached
ReDim Preserve Arr2(1 To myCount)
which now reads
ReDim Preserve Arr2(1 To z)
it didn't work as you designed. With the *correct* variable, though, it
works great!

Thanks again!
Ed

(PS - I'm using this to resolve an issue from an earlier post of mine: how
to create a range that excludes another range ("How to exclude certain cells
from a range?", posted 1/9/2006 - http://tinyurl.com/78vt8)
 
Back
Top