Using a 1d Array to affect a 2d array

[zoog25]

Hello All,

Here is the situation I Have. I have a 1D (Column F) array of X length (68,000) and I have a 2D Array (Column R and S) with of X length (about 75,000). Now i want to compare each entry in Array1 with Array2. When it matches with Array2, it pulls out the information in the second column. So for example row 9 of Array1 "Let says 959" matches up with row 37 of Array2 (959,37). The 37 is placed into a variable called nr. Then after that row 37 of Array2 gets deleted and Array2 is 1 row shorter than before.

The reasoning for this is that as Array1 reaches closer and closer to the end of it's list, Array2 gets smaller and smaller so there is not that many entries that need to be gone through.

Note that there are no duplicates in array2, so once eliminated, the information won't be needed after matching. Thank you for any help

As an extra, once the loop has gone through all of Array1. Array2 can be erased or emptied. I'm new to using arrays.

 

[Akuini]

Hi, zoog25
1. Are the data all numbers?
If yes then can you sort the data by col F then col R ascending?
If yes then we can write a much more efficient code.

Note that there are no duplicates in array2

2. What about array1, are there any duplicates?

 

[igold]

You did not say what you wanted to do with your Variable nr. This code will write nr to Sheet3, starting at Cell A1. If you have not worked with Arrays much, you may not realize that once they are in memory they are blazingly fast. There is not really a need to try to use logic shorten the number of loops. This code is designed to make each loop as short as possible by exiting the loop once the value is found.

Sub ArrayLoop()

    Dim arr1, arr2, nr
    Dim a1 As Long, a2 As Long, x As Long
    
    arr1 = Range("F2:F" & Cells(Rows.Count, 6).End(xlUp).Row)
    arr2 = Range("R2:S" & Cells(Rows.Count, 18).End(xlUp).Row)
    x = 1
    ReDim nr(1 To UBound(arr1), 1 To 1)
    For a1 = 1 To UBound(arr1)
        For a2 = 1 To UBound(arr2)
            If arr1(a1, 1) = arr2(a2, 1) Then
                nr(x, 1) = arr2(a2, 2)
                x = x + 1
                Exit For
            End If
        Next
    Next
    
    Worksheets("Sheet3").Range("A1").Resize(UBound(nr)) = nr
    arr1 = Empty
    arr2 = Empty
    
End Sub

 

[igold]

So I played with this a little. I ran my sample data out to 68,540 rows for Arr1 and 75,959 rows for Arr2 and put a timer on it. If you flip flop the loops as shown below, the code runs about 10% faster. It is not as fast as I thought it would be. On my machine the following code ran in just over 4 minutes (4:12). Your mileage may vary depending on your machine and whatever else is using resources.

Sub ArrayLoop()

'**********************************************************************
    Dim StartTime As Double
    Dim MinutesElapsed As String
    
    'Remember time when macro starts
    StartTime = Timer
'**********************************************************************
    Dim arr1, arr2, nr
    Dim a1 As Long, a2 As Long, x As Long
    
    arr2 = Range("R2:S" & Cells(Rows.Count, 18).End(xlUp).Row)
    arr1 = Range("F2:F" & Cells(Rows.Count, 6).End(xlUp).Row)
    x = 1
    ReDim nr(1 To UBound(arr1), 1 To 1)
    For a2 = 1 To UBound(arr2)
        For a1 = 1 To UBound(arr1)
            If arr1(a1, 1) = arr2(a2, 1) Then
                nr(x, 1) = arr2(a2, 2)
                x = x + 1
                Exit For
            End If
        Next
    Next
    
    Worksheets("Sheet3").Range("A1").Resize(UBound(nr)) = nr
    arr1 = Empty
    arr2 = Empty
'**********************************************************************
    'Determine how many seconds code took to run
    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

    'Notify user in seconds
    MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
    
End Sub

 

[Fluff]

Another option would be to use a dictionary.

Sub zoog()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
   
   Ary = Range("R2:S" & Range("R" & Rows.Count).End(xlUp).Row).Value2
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         .Item(Ary(r, 1)) = Ary(r, 2)
      Next r
      Ary = Range("F2", Range("F" & Rows.Count).End(xlUp)).Value2
      ReDim Nary(1 To UBound(Ary), 1 To 1)
      For r = 1 To UBound(Ary)
         If .Exists(Ary(r, 1)) Then
            nr = nr + 1
            Nary(nr, 1) = .Item(Ary(r, 1))
         End If
      Next r
   End With
   Sheets("sheet1").Range("A2").Resize(nr).Value = Nary
End Sub

 

[zoog25]

thank you for the methods for doing the arrays.
To answer Akuini.

Array1 is made up of an 11 digit number, the issue is that the number could be something like 00145234912 or 014523419812 or 12345678910. It will always be an 11 digit number and it is comparing exact name 11 digit number in Array2 column 1.

Now here is how i'm sorta of running my current program and why i'm asking about 2D arrays because currently what i'm doing is quick at the beginning but as it gets to a higher place in the list it slows down.

I'm using a 1D array from sheet 1 comparing to a 1D array from sheet 2. When both match up, the location in the second array is then given to nr as nr = n + 1 where n is the location in the array. nr is then used to pull information from that particular row in sheet 2.

The information from both arrays is sorted so in theory they should match up together but since we are talking about anywhere from 65,000 to 85,000 entries, as the lop goes further down in Array1, it has to start from the beginning of Array2 and waste time searching through numbers that have already been matched up with. I thought that if I used a 2D array for the second Array then I could have Array1 compare with Array2 column 1. When the matched happened, column 2 of Array2 would already have the row information. Example Array2 column 1 would have the 11 digit entry and column 2 would have something like 37, which is the row on sheet2 that the digit belongs too. Now as each number is matched up between array1 and array2, Array2 would get shorter since matches have already been made and those number are not needed any more. So in theory if we have Array1 be 65 number and Array2 was 65 numbers, in the end Array2 would be empty since all the numbers in Array1 was matched up with Array2.

Hopefully this will help.

 

[Fluff]

What you are asking for would be impractical & slow as you cannot "delete" rows from an array.
The approach I suggested is incredibly fast ~0.5 seconds for 93,000 rows in each array.

I suggest that you explain exactly what you need to happen (not how you think it should work) & post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[igold]

@Fluff You are fast. 0.02 seconds with my data...

 

[Alex Blakenburg]

I'm not 100% clear on how this is being used but if the end result is to line up what is in Column S with what is in Column F, then consider modifying the 2nd loop to the below (increments the counter for "Not Found").
@Fluff - I was hoping someone would use the dictionary - thank you - (also for the late binding example)
@igold - your speed test results were very helpful - thanks

      For r = 1 To UBound(Ary)
         nr = nr + 1
         If .Exists(Ary(r, 1)) Then
            Nary(nr, 1) = .Item(Ary(r, 1))
         Else
            Nary(nr, 1) = "Not Found"
         End If
      Next r

 

[zoog25]

Hello All,

I'm sorry everyone. First off, thank you for letting me know that what I was thinking was incorrect. So here is my question regarding the information presented. In order to make you guys code to work for what i had a question regarding column 2 of the 2D Array. How would i code to say just number that entry in order so the first entry would have the number 2 and the last entry would technically be the last row of the column on the sheet that is being brought into the array.