Hi, i found myself stuck here, so i hope anyone can help me out:]

I have read two delimited lists into two separate arrays. For time being, the arrays arent multidimensional, but thats my next step. My biggest problem tho, is that i cant find any info on how to search in arrays.

Lets say i have two lists

ARRAY A
123;STK;10
456;STK;5
789;STK;100

ARRAY B
345;STK;19
123;STK;15
789;STK;200

I want to take "123" from Array A, find "123" in Array B, and replace the value 10 (3'rd collumn) in Array A with value 15 from Array B.
And so on... If value "123" not found in Array B, keep old value (in 3'rd collumn)

Almost like Vlookup in Excel..

You say the arrays aren't multidimensional but from the way they are listed, they are. Unless 123;STK;10 is a single colon delimited string you already have two dimensions. Columns and Rows. If you were to combine Array A and Array B into a single array you would have a 3 dimensional array - columns, rows and sheets.

You will find that manipulating 3 dimensional arrays in Visual Basic will be extremely slow.

A search of Google using 'sort array Visual Basic' will bring up many array sorting algorithms for VB. However, unless all the numbers in column 1 have corresponding numbers in both arrays, sorting isn't going to help you much - you are still going to have to do some kind of search to find the numbers.

However is the arrays are sorted, you can do a binary chop search which will speed things up a little. Again, google as the answers.

The problems will arise if you start entering or deleting data. Every time something is entered or deleted the array will have to be re-sorted.

I would suggest putting the data into an Access database then using the power of SQL to manipulate the data. All the complexity of indexing, sorting and searching is done for you.

Stuart

Well, atm the array aint multidim. Every entry in the array is "A;B;C" as one entry..
The plan tho, is that i will use the ";" delimiter to split up the lists into a multidimensional array. (I guess that shouldnt be a problem). The list of entries is static. There will be no deleting of entries. All the script is supposed to do is a Vlookup, except that if some value in array A isn't found in array B, keep old value in array A.

In Array A collumn 3 contains outdated consumption, and thats why i want to replace it with the updated consuption from Array B.
Now, i could use Excel for this, but this is only a part of the script, and i want it all to be 100% automated.

When it comes to sorting, it wont help me here i think. This is becuase list A (array A) doesnt nececary have to be identical to list b(array b).
Orignially i made some experiments with this in batch (where i used findstr /g to search values), but its way to slow.

Well, perhaps trying to make a macro in Excel would be easier..

This will actually be easier if the elements are in a multidimensional array. So assuming the values are in two-dimensional arrays (ArrayA & ArrayB) here is some sample code (not tested):

Dim ItemA, ItemB
For each ItemA in ArrayA
For Each ItemB in ArrayB
If ItemA[0] = ItemB[0] Then
ItemA[2] = ItemB[2]
End If
Next
Next

The one thing that may be a problem is what if you have the same "first" value in two elements of ArrayB. Something like this:

ARRAY B
345;STK;19
123;STK;15
789;STK;200
123;STK;89

This may not be an issue based upon the data you will use, but if so only the last value would be sued to overwrite ItemA values.

Michael J

Hi Shr0Om,
Based on what I understood from ur original/first question, I have written the below code quickly.

I wrote it in VB6.U can easily change it to VB script.

This is just to demonstrate how U can search and replace the values in a array containing delimited values.

Private Sub Command1_Click()
Dim StrA(2) As String
Dim StrB(2) As String
Dim SplitStrA() As String
Dim SplitStrB() As String

Dim idxA As Long
Dim idxB As Long

StrA(0) = "123;STK;10"
StrA(1) = "456;STK;5"
StrA(2) = "789;STK;100"

StrB(0) = "345;STK;19"
StrB(1) = "123;STK;15"
StrB(2) = "789;STK;200"

'' Display values before searching
'' this is just for refernce u can remove
For idxA = 0 To UBound(StrA)
Debug.Print StrA(idxA)
Next
For idxB = 0 To UBound(StrB)
Debug.Print StrB(idxB)
Next

'' Loop thru each Array A element
'' If u know the index of the element U like to search in Array B
'' then U can skip the first For Loop
For idxA = 0 To UBound(StrA)
'' Split the delimited values into Array
'' SplitStrA (0)=1 st column
'' SplitStrA (1)=2 nd column
'' SplitStrA (2)=3 rd column
SplitStrA = Split(StrA(idxA), ";")
'' Serach fist column in ARRAY B
For idxB = 0 To UBound(StrB)
'' Compare current record's First Column of Array B with the Splitted Variable
If SplitStrA(0) = Left(StrB(idxB), Len(SplitStrA(0))) Then
'' Now split variable B, (this is the Lazy way , else use InStrRev() function)
SplitStrB = Split(StrB(idxB), ";")
'' Replace column three of Array A
StrA(idxA) = SplitStrA(0) & ";" & SplitStrA(1) & ";" & SplitStrB(2)
End If
Next
Next
'' Display values after searching
'' this is just for refernce u can remove
For idxA = 0 To UBound(StrA)
Debug.Print StrA(idxA)
Next
For idxB = 0 To UBound(StrB)
Debug.Print StrB(idxB)
Next
End Sub

All the Best
Tell me if this was useful..
Or else I will try different approach

Samuel Roshan

Thnx for the help everyone. Both the pseudo code, and the VB6 code gives me a good reference of what to do.

SamRock: Im pretty much a noob in VBscript, but i will see if i cant translate it into VBscript. It does looks pretty alike. Anyway, im sure your code will come in handy:)