Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with arrays?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with arrays?
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. -- Regards, Tom Ogilvy "Ed" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with arrays?
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 a " & Left(myMsg, Len(myMsg) - 2) End Sub "Ed" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with arrays?
Beautiful, Bernie! Thank you!!
Ed "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 a " & Left(myMsg, Len(myMsg) - 2) End Sub "Ed" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with arrays?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with arrays?
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with arrays?
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 "Ed" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with arrays?
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) "Ed" wrote in message ... 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrays | Excel Worksheet Functions | |||
Arrays | Excel Discussion (Misc queries) | |||
Help with arrays please | Excel Programming | |||
Arrays | Excel Programming | |||
Arrays | Excel Programming |