Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing elements from 2 arrays using VBA code
Hi,
I am having a bit of trouble with arrays. I have created two list arrays which are of different sizes and have some of the same numbers in them. I want to go through one list and compare every element within it with every element on the second list. I need to find out (basically) which numbers are in the first array but not the second. The outline of the code i am using is as follows: i = 1 j = 1 For i = LBound(array1) To UBound(array1) Do While array1(i) < array2(j) For j = LBound(array2) To UBound(array2) If j = UBound(array2) Then ~~~Return Some Values~~~ GoTo GetOut Else: End If Next j Loop GetOut: Next i The problem lies in that although there are no bugs in my code the macro is returning all the data (as if the lists had nothing in common). Hope someone can help me!! Thanks a lot Tom ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing elements from 2 arrays using VBA code
Thanks for your help it seems like your method is more powerful... but i
think the problem i may have been having is that for some reason on array element would be displayed as "24" and another as 24 and the coding then said they are different. But ive sorted that now Thanks for your help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing elements from 2 arrays using VBA code
24 is different from "24"
In some cases, excel will coerce the comparison to the same data type and the results would be true. I assume there are other cases where it won't. -- Regards, Tom Ogilvy "lopsided" wrote in message ... Thanks for your help it seems like your method is more powerful... but i think the problem i may have been having is that for some reason on array element would be displayed as "24" and another as 24 and the coding then said they are different. But ive sorted that now Thanks for your help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing elements from 2 arrays using VBA code
Just another idea. I think you are working with only numbers.
... which numbers are in the first array but not the second. This returns the Complement, those that are in A, and not in B. (4,11,12 & 13) I'm not sure how fast this would be with large arrays though. Sub Complement() Dim i As Long Dim t As Long Dim p As Long Dim v1 As Variant Dim v2 As Variant Dim v3 As Variant v1 = Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13) v2 = Array(5, 6, 7, 8, 9, 10) ReDim v3(0 To UBound(v1)) p = 0 On Error Resume Next With WorksheetFunction For i = LBound(v1) To UBound(v1) t = .Match(v1(i), v2, 0) If t = 0 Then v3(p) = v1(i) p = p + 1 Else t = 0 End If Next i End With ReDim Preserve v3(1 To p) End Sub -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "lopsided" wrote in message ... Hi, I am having a bit of trouble with arrays. I have created two list arrays which are of different sizes and have some of the same numbers in them. I want to go through one list and compare every element within it with every element on the second list. I need to find out (basically) which numbers are in the first array but not the second. The outline of the code i am using is as follows: i = 1 j = 1 For i = LBound(array1) To UBound(array1) Do While array1(i) < array2(j) For j = LBound(array2) To UBound(array2) If j = UBound(array2) Then ~~~Return Some Values~~~ GoTo GetOut Else: End If Next j Loop GetOut: Next i The problem lies in that although there are no bugs in my code the macro is returning all the data (as if the lists had nothing in common). Hope someone can help me!! Thanks a lot Tom ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing ranges/arrays | Excel Worksheet Functions | |||
Elements and Arrays in Excel | Excel Worksheet Functions | |||
Comparing 2 arrays | Excel Worksheet Functions | |||
Comparing Arrays | Excel Discussion (Misc queries) | |||
Comparing Arrays | Excel Worksheet Functions |