Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Compare and update arrays

A1:B3 held your Array2 values
C1:D3 held your Array1 values

Added 3 blank cells to the range for Array2 so it had space for additions.

Sub Wash()

iCount = 1
iRow = 1
Array1 = Range("C1:D3").Value
Array2 = Range("A1:B6").Value
Do While iRow <= UBound(Array1, 1)
'if elements in both arrays are equal then move on...
bAdded = False
Do While Trim(Array1(iRow, 1)) < Trim(Array2(iCount, 1))
'but if not, then check if array2 is empty
If IsEmpty(Array2(iCount, 1)) = True Then
'if empty, then add the new element to array2
Array2(iCount, 1) = Trim(Array1(iRow, 1))
Array2(iCount, 2) = Trim(Array1(iRow, 2))
bAdded = True
Exit Do
End If
'Otherwise, if the array is not empty, then increment the
' counter in array2
iCount = iCount + 1
' Loop back, checking each of the elements in array2 for a match
' with array1. If no match is found then the value
' is added to array2 as above
Loop
' Now if the compared elements match, then assign
' the other values to array2
If Not bAdded Then
If Trim(Array2(iCount, 1)) = Trim(Array1(iRow, 1)) Then
Array2(iCount, 2) = Trim(Array1(iRow, 2))
End If
End If
'Now that the values have been assigned, move on to
' the next element in array1
iRow = iRow + 1
iCount = 1
Loop
Range("A10:B15").Value = Array2
End Sub

this produced the results you show.
--
Regards,
Tom Ogilvy


"DAO" wrote in message
ups.com...
Hi Tom,

Yea I guess I declared it incorrectly. They should be 2 dimensional
arrays (3x2). Elements (1,1) = "a", (1,2) = 1, (2,1) = "b" etc



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I compare values in two arrays in Excel? Irrylath Excel Discussion (Misc queries) 1 August 1st 08 12:51 AM
Compare arrays mavxob Excel Worksheet Functions 5 March 6th 08 10:55 PM
Compare arrays youngster Excel Worksheet Functions 3 July 31st 07 04:36 PM
canceling the formula aut-update feature with Arrays Marshall.tway Excel Discussion (Misc queries) 1 November 29th 05 04:56 PM
Arrays: compare fields? NorTor[_4_] Excel Programming 1 February 17th 05 10:29 AM


All times are GMT +1. The time now is 12:55 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"