how to compare very large arrays and ReDim based on comparisons?

[kor]

Below is a sample of what I'm looking for. I have 2 arrays that are very large (several thousand rows) and I need to compare values between them.

I have an input array ($aAD) and an Array that I'm comparing it to ($aGenesis)

$aGenesis would be treated as the "correct" information. So if a row ($i) has any value that is different to a corresponding row in $aGenesis it should be loaded into the new array and an appropriate "add, delete, move" should be appended to column 6 in the new array.

If a row with username X is in $aAD but not in $aGenesis that would be action "delete"

If a row with username X has a different value for any column with the same corresponding username X in $aGenesis that would be action "change/update"

If a row with username X in $aGenesis does not exist in $sAD that would be a "create"

I have no problem with the code after I have all the information loaded into $aFinal. I already have code built if the actions are add, delete, change, move, etc.

What I've never had to do before is compare two arrays .. but not only compare them.. look at every single value in them and compare with every single value in the other array.

#include <Array.au3>

Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"], ["John", "Smith", "3456", "Location1", "9th"], _
["Jason", "Jackson", "5678", "Location4", "4th"], ["Chris", "Black", "4445", "Location2", "2nd"], ["Lee", "Chan", "9876", "Location1", "2nd"]]

Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"], ["John", "Smith", "3456", "Location1", "9th"], _
["Jason", "Jackson", "5678", "Location1", "2nd"]]

_ArrayDisplay($aAD)
_ArrayDisplay($aGenesis)

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him

Global $aFinal[3][6] = [["Jason", "Jackson", "5678", "Location1", "2nd", "Move"], ["Chris", "Black", "4445", "Location2", "2nd", "Delete"], _
["Lee", "Chan", "9876", "Location1", "2nd", "Delete"]]

_ArrayDisplay($aFinal)

[water]

And the question is?

[guinness]

I think he wants code written for him. Search _ArrayDiff.

[kor]

And the question is?

the question is the title of the thread.

how to compare very large arrays and ReDim based on comparisons?

EDIT:

is _ArrayDiff a custom udf? It isn't in the help file.

Edited August 10, 2012 by kor

[water]

A first (slow) example could look like:

expandcollapse popup

#include <Array.au3>

Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location4", "4th"],["Chris", "Black", "4445", "Location2", "2nd"],["Lee", "Chan", "9876", "Location1", "2nd"]]

Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location1", "2nd"]]

_ArrayDisplay($aAD)
_ArrayDisplay($aGenesis)

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him
$iFinal = 0
Global $aFinal[1][6]
For $iAD = 0 To UBound($aAD, 1) - 1
    $bFound = False
    For $iGenesis = 0 To UBound($aGenesis, 1) - 1
        If $aAD[$iAD][0] = $aGenesis[$iGenesis][0] And $aAD[$iAD][1] = $aGenesis[$iGenesis][1] Then  ; Name found in $aGenesis
            $bFound = True
            If $aAD[$iAD][2] <> $aGenesis[$iGenesis][2] Or $aAD[$iAD][3] <> $aGenesis[$iGenesis][3] Or $aAD[$iAD][4] <> $aGenesis[$iGenesis][4] Then ; Content different
                If $iFinal > 0 Then ReDim $aFinal[UBound($aFinal, 1)+1][6]
                $aFinal[$iFinal][0] = $aGenesis[$iGenesis][0]
                $aFinal[$iFinal][1] = $aGenesis[$iGenesis][1]
                $aFinal[$iFinal][2] = $aGenesis[$iGenesis][2]
                $aFinal[$iFinal][3] = $aGenesis[$iGenesis][3]
                $aFinal[$iFinal][4] = $aGenesis[$iGenesis][4]
                $aFinal[$iFinal][5] = "Move"
                $iFinal = $iFinal + 1
            Endif
        EndIf
    Next
    If $bFound = False Then ; Name not found
        If $iFinal > 0 Then ReDim $aFinal[UBound($aFinal, 1)+1][6]
        $aFinal[$iFinal][0] = $aAD[$iAD][0]
        $aFinal[$iFinal][1] = $aAD[$iAD][1]
        $aFinal[$iFinal][2] = $aAD[$iAD][2]
        $aFinal[$iFinal][3] = $aAD[$iAD][3]
        $aFinal[$iFinal][4] = $aAD[$iAD][4]
        $aFinal[$iFinal][5] = "Delete"
        $iFinal = $iFinal + 1
    Endif
Next

;Global $aFinal[3][6] = [["Jason", "Jackson", "5678", "Location1", "2nd", "Move"],["Chris", "Black", "4445", "Location2", "2nd", "Delete"], _
;        ["Lee", "Chan", "9876", "Location1", "2nd", "Delete"]]

_ArrayDisplay($aFinal)

[water]

And here the version without "ReDim":

expandcollapse popup

#include <Array.au3>

Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location4", "4th"],["Chris", "Black", "4445", "Location2", "2nd"],["Lee", "Chan", "9876", "Location1", "2nd"]]

Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location1", "2nd"]]

_ArrayDisplay($aAD)
_ArrayDisplay($aGenesis)

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him
$iFinal = 0
Global $aFinal[UBound($aAD, 1) - 1][6]
For $iAD = 0 To UBound($aAD, 1) - 1
    $bFound = False
    For $iGenesis = 0 To UBound($aGenesis, 1) - 1
        If $aAD[$iAD][0] = $aGenesis[$iGenesis][0] And $aAD[$iAD][1] = $aGenesis[$iGenesis][1] Then  ; Name found in $aGenesis
            $bFound = True
            If $aAD[$iAD][2] <> $aGenesis[$iGenesis][2] Or $aAD[$iAD][3] <> $aGenesis[$iGenesis][3] Or $aAD[$iAD][4] <> $aGenesis[$iGenesis][4] Then ; Content different
                $aFinal[$iFinal][0] = $aGenesis[$iGenesis][0]
                $aFinal[$iFinal][1] = $aGenesis[$iGenesis][1]
                $aFinal[$iFinal][2] = $aGenesis[$iGenesis][2]
                $aFinal[$iFinal][3] = $aGenesis[$iGenesis][3]
                $aFinal[$iFinal][4] = $aGenesis[$iGenesis][4]
                $aFinal[$iFinal][5] = "Move"
                $iFinal = $iFinal + 1
            Endif
        EndIf
    Next
    If $bFound = False Then ; Name not found
        $aFinal[$iFinal][0] = $aAD[$iAD][0]
        $aFinal[$iFinal][1] = $aAD[$iAD][1]
        $aFinal[$iFinal][2] = $aAD[$iAD][2]
        $aFinal[$iFinal][3] = $aAD[$iAD][3]
        $aFinal[$iFinal][4] = $aAD[$iAD][4]
        $aFinal[$iFinal][5] = "Delete"
        $iFinal = $iFinal + 1
    Endif
Next
ReDim $aFinal[$iFinal][6]

_ArrayDisplay($aFinal)

[kor]

@water, I'll load up your code and see what it does.

I did find this :

but the inputs can only be 1D arrays.

[MilesAhead]

It sounds like you need to use a different method of data storage/retrieval. You may want to investigate SQL and do things with queries. Or even some form of balanced tree structure where the data is retrieved in fewer operations. Brute force permutation is bound to be slowest.

[BrewManNH]

If sorting the arrays isn't an issue, then a binary search of the array would be the fastest way to check one array against another. You'd loop through one array, and search for the item in the other using this modified version of _ArrayBinarySearch

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayBinarySearch2D
; Description ...: Uses the binary search algorithm to search through a 1 or 2-dimensional array.
; Syntax.........: _ArrayBinarySearch2D(Const ByRef $avArray, $vValue[, $iColumn = 0[, $iStart = 0[, $iEnd = 0]]])
; Parameters ....: $avArray - Array to search
;                 $vValue  - Value to find
;                 $iColumn - [optional] Which column to search on [Default = First (0) column]
;                 $iStart  - [optional] Index of array to start searching at [Default = start of array]
;                 $iEnd    - [optional] Index of array to stop searching at [Default = end of array]
; Return values .: Success - Index that value was found at
;                 Failure - -1, sets @error to:
;                 |1 - $avArray is not an array
;                 |2 - $vValue outside of array's min/max values
;                 |3 - $vValue was not found in array
;                 |4 - $iStart is greater than $iEnd
;      |5 - $iColumn is greater than actual number of columns
;                 |6 - $avArray has too many dimensions
; Author ........: Jos van der Zande <jdeb at autoitscript dot com>
; Modified.......: Ultima - added $iEnd as parameter, code cleanup
; Modified.......: BrewManNH - added ability to search a 2D array
; Remarks .......: When performing a binary search on an array of items, the contents of the column being searched MUST be
;                 sorted before the search is done, otherwise undefined results will be returned.
; Related .......: _ArrayFindAll, _ArraySearch, _ArrayBinarySearch
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ArrayBinarySearch2D(Const ByRef $avArray, $vValue, $iColumn = 0, $iStart = 0, $iEnd = 0)
 If UBound($avArray, 0) > 2 Then Return SetError(6, 0, -1)
 Local $i2D = False
 If UBound($avArray, 0) > 1 Then
  $i2D = True
 EndIf
 If UBound($avArray, 2) < $iColumn Then Return SetError(5, 0, -1)
 If Not IsArray($avArray) Then Return SetError(1, 0, -1)
 Local $iUBound = UBound($avArray) - 1
 ; Bounds checking
 If $iEnd < 1 Or $iEnd > $iUBound Then $iEnd = $iUBound
 If $iStart < 0 Then $iStart = 0
 If $iStart > $iEnd Then Return SetError(4, 0, -1)
 Local $iMid = Int(($iEnd + $iStart) / 2)
 If Not $i2D Then
  If $avArray[$iStart] > $vValue Or $avArray[$iEnd] < $vValue Then Return SetError(2, 0, -1)
  ; Search
  While $iStart <= $iMid And $vValue <> $avArray[$iMid]
   If $vValue < $avArray[$iMid] Then
    $iEnd = $iMid - 1
   Else
    $iStart = $iMid + 1
   EndIf
   $iMid = Int(($iEnd + $iStart) / 2)
  WEnd
  If $iStart > $iEnd Then Return SetError(3, 0, -1) ; Entry not found
  Return $iMid
 Else
  If $avArray[$iStart][$iColumn] > $vValue Or $avArray[$iEnd][$iColumn] < $vValue Then Return SetError(2, 0, -1)
  ; Search
  While $iStart <= $iMid And $vValue <> $avArray[$iMid][$iColumn]
   If $vValue < $avArray[$iMid][$iColumn] Then
    $iEnd = $iMid - 1
   Else
    $iStart = $iMid + 1
   EndIf
   $iMid = Int(($iEnd + $iStart) / 2)
  WEnd
  If $iStart > $iEnd Then Return SetError(3, 0, -1) ; Entry not found
  Return $iMid
 EndIf
EndFunc   ;==>_ArrayBinarySearch2D

I use this function in a media player to search for duplicate media files when adding a folder to the player, even when the music list is over 4000 entries long and the array I'm looking to compare the known list against is 4000+ entries, this search still takes less than a second or 2, and I only use ReDim once when it's done comparing.

[KaFu]

Here's my approach...

Edit 1: Upsa, does not meet the request , will re-post in a couple of minutes...

Edit 2: This should be better...

expandcollapse popup

#include <Array.au3>

Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location4", "4th"],["Chris", "Black", "4445", "Location2", "2nd"],["Lee", "Chan", "9876", "Location1", "2nd"]]

Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location1", "2nd"]]

; _ArrayDisplay($aAD)
; _ArrayDisplay($aGenesis)

Global $oDict_Compare = ObjCreate('Scripting.Dictionary')
$oDict_Compare.CompareMode = 1
Global $i_Results = 0, $s_Value, $a_Value
Global $aFinal[500][6]

For $i = 0 To UBound($aGenesis) - 1
    $oDict_Compare.Add($aGenesis[$i][0] & "|||" & $aGenesis[$i][1], $aGenesis[$i][2] & "|||" & $aGenesis[$i][3] & "|||" & $aGenesis[$i][4])
Next


For $i = 0 To UBound($aAD) - 1
    If $oDict_Compare.Exists($aAD[$i][0] & "|||" & $aAD[$i][1]) Then
        $s_Value = $oDict_Compare.Item($aAD[$i][0] & "|||" & $aAD[$i][1])
        If $s_Value <> $aAD[$i][2] & "|||" & $aAD[$i][3] & "|||" & $aAD[$i][4] Then ; move
            $i_Results += 1
            If Mod($i_Results, 500) Then ReDim $aFinal[UBound($aFinal) + 500][6]
            $a_Value = StringSplit($s_Value, "|||", 1)
            $aFinal[$i_Results - 1][0] = $aAD[$i][0]
            $aFinal[$i_Results - 1][1] = $aAD[$i][1]
            $aFinal[$i_Results - 1][2] = $a_Value[1]
            $aFinal[$i_Results - 1][3] = $a_Value[2]
            $aFinal[$i_Results - 1][4] = $a_Value[3]
            $aFinal[$i_Results - 1][5] = "Move"
        EndIf
    Else ; Delete
        $i_Results += 1
        If Mod($i_Results, 500) Then ReDim $aFinal[UBound($aFinal) + 500][6]
        $aFinal[$i_Results - 1][0] = $aAD[$i][0]
        $aFinal[$i_Results - 1][1] = $aAD[$i][1]
        $aFinal[$i_Results - 1][2] = $aAD[$i][2]
        $aFinal[$i_Results - 1][3] = $aAD[$i][3]
        $aFinal[$i_Results - 1][4] = $aAD[$i][4]
        $aFinal[$i_Results - 1][5] = "Delete"
    EndIf
Next

ReDim $aFinal[$i_Results][6]

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him

#cs
    Global $aFinal[3][6] = [["Jason", "Jackson", "5678", "Location1", "2nd", "Move"],["Chris", "Black", "4445", "Location2", "2nd", "Delete"], _
    ["Lee", "Chan", "9876", "Location1", "2nd", "Delete"]]
#ce

_ArrayDisplay($aFinal)

Edited August 10, 2012 by KaFu

[MilesAhead]

Google showed but clicking the link did not get a response. A tree implementation will be slower loading, but the item retrieveal should be much faster.

[kor]

@water, your code "works" but seems to break down when I feed it information other than the sample array in the code.

I've attached a sample output in CSV of my AD and Genesis outputs.

If everything would work right with your code then rows 0 - 24 would be ignores, rows 25 - 28 would be moves, row 29 would be a delete, and rows 30 and 31 would be an adds.

@Kafu, I tried your code and it took over a minute with my CPU pegged and it still didn't complete so I scraped it. (i fed it my actual data at 3000+ rows)

genesis.txt

ad.txt

expected output.txt

[water]

How do you fill the arrays with the specified data?

[kor]

How do you fill the arrays with the specified data?

during the loops? Or are you talking about in general.. IE where are the output files being generated from?

[MilesAhead]

Another simpler alternative.. if the UserID is guaranteed to be unique, or can be made so, you can use Scripting.Dictionary and just look up the User's data with the UserID as the key. Can't get much simpler. Also it's likely to be more sophisticated lookup than looping through the entire data set for each lookup.

Here's a basic test app to show how to create it, access items by key, and delete it

$aList = ObjCreate("Scripting.Dictionary")
If @error Then
    MsgBox(0,@ScriptName,"ObjCreate failed")
    Exit
EndIf

$aList("somestuff") = "somestuff to store in array"
$aList("morestuff") = "morestuff to store in array"

MsgBox(0,@ScriptName,$aList("somestuff"))
MsgBox(0,@ScriptName,$aList("morestuff"))
$aList.RemoveAll()
$aList = 0

Edited August 10, 2012 by MilesAhead

[water]

At the start of the script you need to fill arrays $aAD and $aGenesis with the example data you posted above. Do you read and parse the files or do you enter the data manually?

[kor]

Another simpler alternative.. if the UserID is guaranteed to be unique, or can be made so, you can use Scripting.Dictionary and just look up the User's data with the UserID as the key. Can't get much simpler. Also it's likely to be more sophisticated lookup than looping through the entire data set for each lookup.

The userid is unique.

However KaFu's code utilized Scripting.Dictionary and when I fed it my 3000+ row array his code didn't work. just pegged my cpu.

@water, the Genesis array is pulled in using "FileRead" then formatted to get everything how it appears in my genesis.txt

The AD file is actually pulled from Active Directory on the fly.

Here are my two functions for formatting both sets of data.

Func _FormatGenesis("C:genesis.txt")
    $sInput = FileRead($sInput) ; read csv file
    If @error = 1 Then ConsoleWrite("ERROR - Unable to open input file" & @CR)
    Local $iRows = StringSplit($sInput, @CR) ; make each line a new row
    Global $aRaw[$iRows[0] + 1][5] ; create raw array with dimensions based on rows
    For $i = 1 To $iRows[0]
        Local $aTemp = StringSplit($iRows[$i], ",", 2) ; create temporary array to split all values into
        For $n = 0 To UBound($aTemp) - 1
            $aRaw[$i][$n] = $aTemp[$n] ; populate array with split data
        Next
    Next
    ;_ArrayDelete($aRaw, UBound($aRaw) - 1) ; delete the last line because it is always blank
    _ArrayDelete($aRaw, 0) ; delete row count in element 0
    FileClose($sInput)
    Return $aRaw
EndFunc   ;==>_FormatGenesis


Func _FormatAD()
    _AD_Open()
    Local $aRaw = _AD_GetObjectsInOU("", "(&(memberof:1.2.840.113556.1.4.1941:=CN=All Student Users,OU=a,DC=a,DC=b,DC=c)" & _
    "(objectCategory=user)(&(!cn=family)(!sn=user)(!sn=student)(!sn=kindergarten)(!sn=1st grade)))", 2, "givenName,sn,sAMAccountName,distinguishedName,extensionAttribute3", "givenName")
    For $i = 1 To UBound($aRaw) - 1
        If StringInStr($aRaw[$i][3], "location1") > 0 Then $aRaw[$i][3] = "location1"
        If StringInStr($aRaw[$i][3], "location2") > 0 Then $aRaw[$i][3] = "location2"
        If StringInStr($aRaw[$i][3], "location3") > 0 Then $aRaw[$i][3] = "location3"
        If StringInStr($aRaw[$i][3], "location4") > 0 Then $aRaw[$i][3] = "location4"
        If StringInStr($aRaw[$i][3], "location5") > 0 Then $aRaw[$i][3] = "location5"
    Next
    _AD_Close()
    _ArrayDelete($aRaw, 0) ; delete row count in element 0
    Return $aRaw
EndFunc   ;==>_FormatAD

Edited August 10, 2012 by kor

[KaFu]

expandcollapse popup

#include <Array.au3>

$a_Input = StringSplit(FileRead("ad.txt"), @LF)
Global $aAD[$a_Input[0]][5]
For $i = 1 To $a_Input[0]
    $a_temp = StringSplit($a_Input[$i], ",")
    For $j = 0 To 4
        $aAD[$i - 1][$j] = $a_temp[$j + 1]
    Next
Next

$a_Input = StringSplit(FileRead("genesis.txt"), @LF)
Global $aGenesis[$a_Input[0]][5]
For $i = 1 To $a_Input[0]
    $a_temp = StringSplit($a_Input[$i], ",")
    For $j = 0 To 4
        $aGenesis[$i - 1][$j] = $a_temp[$j + 1]
    Next
Next

#cs
    Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
    ["Jason", "Jackson", "5678", "Location4", "4th"],["Chris", "Black", "4445", "Location2", "2nd"],["Lee", "Chan", "9876", "Location1", "2nd"]]
#ce

#cs
    Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
    ["Jason", "Jackson", "5678", "Location1", "2nd"]]
#ce

; _ArrayDisplay($aAD)
; _ArrayDisplay($aGenesis)

Global $oDict_Compare = ObjCreate('Scripting.Dictionary')
$oDict_Compare.CompareMode = 1
Global $s_Value, $a_Value

Global $aFinal[UBound($aAD)][6]

For $i = 0 To UBound($aGenesis) - 1
    $oDict_Compare.Add($aGenesis[$i][0] & "|||" & $aGenesis[$i][1], $aGenesis[$i][2] & "|||" & $aGenesis[$i][3] & "|||" & $aGenesis[$i][4])
Next


For $i = 0 To UBound($aAD) - 1
    If $oDict_Compare.Exists($aAD[$i][0] & "|||" & $aAD[$i][1]) Then
        $s_Value = $oDict_Compare.Item($aAD[$i][0] & "|||" & $aAD[$i][1])
        If $s_Value <> $aAD[$i][2] & "|||" & $aAD[$i][3] & "|||" & $aAD[$i][4] Then ; move
            $a_Value = StringSplit($s_Value, "|||", 1)
            $aFinal[$i][0] = $aAD[$i][0]
            $aFinal[$i][1] = $aAD[$i][1]
            $aFinal[$i][2] = $a_Value[1]
            $aFinal[$i][3] = $a_Value[2]
            $aFinal[$i][4] = $a_Value[3]
            $aFinal[$i][5] = "Move"
        Else ; ignore
            $aFinal[$i][0] = $aAD[$i][0]
            $aFinal[$i][1] = $aAD[$i][1]
            $aFinal[$i][2] = $aAD[$i][2]
            $aFinal[$i][3] = $aAD[$i][3]
            $aFinal[$i][4] = $aAD[$i][4]
            $aFinal[$i][5] = "Ignore"
        EndIf
        $oDict_Compare.Remove($aAD[$i][0] & "|||" & $aAD[$i][1])
    Else ; Delete
        $aFinal[$i][0] = $aAD[$i][0]
        $aFinal[$i][1] = $aAD[$i][1]
        $aFinal[$i][2] = $aAD[$i][2]
        $aFinal[$i][3] = $aAD[$i][3]
        $aFinal[$i][4] = $aAD[$i][4]
        $aFinal[$i][5] = "Delete"
    EndIf
Next
$iCount_Old = UBound($aFinal)
ReDim $aFinal[UBound($aFinal) + $oDict_Compare.Count()][6]
$i_Enum = 0
For $i In $oDict_Compare.Keys()
    $a_Value = StringSplit($i, "|||", 1)
    $aFinal[$iCount_Old + $i_Enum][0] = $a_Value[1]
    $aFinal[$iCount_Old + $i_Enum][1] = $a_Value[2]
    $a_Value = StringSplit($oDict_Compare.Item($i), "|||", 1)
    $aFinal[$iCount_Old + $i_Enum][2] = $a_Value[1]
    $aFinal[$iCount_Old + $i_Enum][3] = $a_Value[2]
    $aFinal[$iCount_Old + $i_Enum][4] = $a_Value[3]
    $aFinal[$iCount_Old + $i_Enum][5] = "Add"
    $i_Enum += 1
Next

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him

#cs
    Global $aFinal[3][6] = [["Jason", "Jackson", "5678", "Location1", "2nd", "Move"],["Chris", "Black", "4445", "Location2", "2nd", "Delete"], _
    ["Lee", "Chan", "9876", "Location1", "2nd", "Delete"]]
#ce

_ArrayDisplay($aFinal)

[kor]

@Kafu... wow!

Your code works. If you have the time could you add some comments as to what the code is doing?

I am not familiar with the objects and calls your code is making so I'm very interested to learn.

[MilesAhead]

The userid is unique.

However KaFu's code utilized Scripting.Dictionary and when I fed it my 3000+ row array his code didn't work. just pegged my cpu.

That would seem to indicate a 3000+ row array is not the way to go. A tool that knows how to store the data on disk and performs efficient caching may take all the load off your shoulders. These problems have been solved decades ago. Why roll your own now? For trivial data sets then sure, just doing it out of your head is faster than learning to use some tool. But when it's gets large better to use the tool designed by the egghead who does nothing else but.