Let me start off by saying I’m completely new to this.

I have two lists, one that is full of parts without homes and one that is full of locations that are either empty or full. I need to go through the list of parts, roughly 15,000, and use a specific code (think “1A8”) in the same row to compare to another list.

The other list is full of locations, whether or not they’re full, that same specific code, which company they belong to and if it’s a high or low location, all of which is needed information.

The end result that I’m trying to achieve is that I go through the list of parts and find each one a location that is unused, in a low location, with a matching specific code, with the same company for both the part and the location; but I can’t use each unused location more than once. Once it finds one, it needs to move to the next location using those criteria.

Set msl = Worksheets("Mass Slot List")
Set lst = Worksheets("Location Storage Types")

aRow = wsl.Range("A50000").End(xlUp).Row
bRow = lst.Range("A120000").End(xlUp).Row

For x = 2 To aRow

    If msl.Range("AQ" & x) = "1A8" Then
        For y = 1 To bRow
            If lst.Range("B" & y) = "1A8" And lst.Range("C" & y) = "L" And lst.Range("D" & y) = "No" And          lst.Range("E" & y) = msl.Range("A" & x) Then
                msl.Range("C" & x) = lst.Range("A" & y)
                
                Exit For
            End If
        Next y
    End If
Next x

I tried the above code, but it simply ran for a bit and crashed my Excel (the amount of data in the file already makes just saving it a 5-10 second endeavor). If I have to create a For loop for every individual code (think “1A8, “5K2”, etc.) then this will take forever to run, if it runs at all, as it loops through everything in the known universe; there are 20+ specific code locations. I also realized I have no way for it to not create duplicates in the code itself.

I’ve seen the .Find function used in other answers that are remotely similar, but I’m not sufficiently tech-savvy to figure out the proper usage for it. I can provide more information if it is needed.