I need to find a row based on multiple criteria, with a catch.

[rcrumbliss]

I have been working on this for a couple days and it is frustrating. I have found many Posts about simple matching of criteria, but I cant figure how to make any of those methods work for my situation.

I have 2 worksheets in my book. The first worksheet is a master list of addresses. The address format for these is like this...

Loop	Sequence	Low Address	High Address	Prefix	Street Name	Suffix
		1000	1500	W	Anywhere	St

The second worksheet is a list of specific addresses formatted like this...

Loop	Sequence	Street Number	Prefix	Street Name	Suffix	Consignee

The problem I have is I need to look up the information in Sheet 2, find the specific row in Sheet 1 which pertains to the address in each row. So I need to look at the Street name, and then check if the street number falls within the Low/High range for that street. I have over 25000 address ranges to look through. The current method I have been using is very slow, taking 2 hours and more to look up the 1000 addresses in Sheet 2. I need to speed this up, but I am not finding a way to do it that works.

Can someone please help. Below is the code I am currently using. I cannot include the source data I am using due to privacy issues.

Public Sub ProcessFinal()

Dim OFile As Worksheet: Set OFile = Sheets("OUTPUT FILE")  'output file
Dim EC As Worksheet:    Set EC = Sheets("ENTIRE CENTER")         'Entire Center
Dim IC As Worksheet     'Imported Closures
Dim CD As Worksheet     'Copy Data from PFT
Dim i As Integer        'Counter
Dim DupRow As Integer   'to save row of duplicates
Dim LoopNum, SeqNum As String
Dim FoundRow As Integer


For i = 2 To [match(2,1/(a:a<>""))]
    If OFile.Range("D" & i).Value = "@@" Then LoopNum = 0 Else LoopNum = OFile.Range("D" & i).Value
    If OFile.Range("E" & i).Value = "@@" Then SeqNum = 0 Else SeqNum = OFile.Range("E" & i).Value
    FoundRow = CheckAdd(OFile.Range("G" & i), OFile.Range("H" & i), OFile.Range("I" & i), OFile.Range("J" & i), OFile.Range("K" & i))
    
        If OFile.Range("D" & i) = EC.Range("C" & FoundRow) And OFile.Range("E" & i) = EC.Range("D" & FoundRow) Then
            'Loop and Sequence are a match move on to next step
        Else
            'Loop and Sequence do not match, add new Loop/Seq to row "O" and update exclude
            OFile.Range("N" & i) = "Yes"
            OFile.Range("O" & i) = "Relooped, new Loop/Seq is : " & EC.Range("C" & FoundRow) & "/" & EC.Range("D" & FoundRow)
            OFile.Range("P" & i) = "Verify New L/S in CLO if needed"
        End If
            
Next
    


End Sub


Public Function CheckAdd(StrNum As String, Pfx As String, Street As String, Sfx As String, StrType As String) As Integer

Dim OFile As Worksheet: Set OFile = Sheets("OUTPUT FILE")  'output file
Dim EC As Worksheet:    Set EC = Sheets("ENTIRE CENTER")         'Entire Center
Dim IC As Worksheet     'Imported Closures
Dim CD As Worksheet     'Copy Data from PFT
Dim rng As Range
Dim StreetNum As Long

StreetNum = CLng(StrNum)
    On Error Resume Next
    Err.Number = 0
    Set rng = EC.Range("J:J").Find(What:=Street, LookIn:=xlValues)
    rng.Find What:=Street, LookIn:=xlValues
    myRow = rng.Row
        
    Do While myRow < EC.[match(2,1/(a:a<>""))]
    If EC.Range("F" & myRow) <= StreetNum And EC.Range("G" & myRow) >= StreetNum Then
        'Call MsgBox("Match Found for" & StrNum & " " & Street & "at row: " & myRow & " of the Entire Center")
        CheckAdd = myRow
        Exit Function
    Else
        'MsgBox "No"
    End If
    Set rng = EC.Range("J:J").FindNext(rng)
    myRow = rng.Row
    Loop
    
End Function

 

[rcrumbliss]

awoohaw, thank you again for your help. I have finally figured out what was going wrong. Here is my formula...

=LET(lkadd,G9,lowadds,ENTIRE_CENTER!$F$1:$F$4376,lkname,I9,stnames,ENTIRE_CENTER!$J$1:$J$4376,rowmatch,SUM((TEXT(lkadd,"0")=lowadds)*(lkname=stnames)*(SEQUENCE(COUNTIF(lowadds,"?*")))),IF(rowmatch=0,"no match","match at row " & rowmatch))

This is working, but I have a follow up questions I hope you can help me with.

In my ENTIRE_CENTER sheet, the number of rows can change over time. The function breaks if I do not have the exact range in the function, as in !$F$1:$F$#####. I cannot figure out how to make the formula look at all the cells in row F that contain data, and ignore blanks cells so that as the number of addresses changes, I dont have to change the formulas every time.

 

[awoohaw]

the FILTER function will create an array that allows you to remove rows that have blanks in the column you're concerned about.

But you need to filter both the lowadds and stnames lists by the same filter. To make it easy to follow I created two new filtered lists for you to use.
you took the High Address Range out and you have a countif instead of the rows function. I've added the filtering tweak I mentioned above the formula I posted in POST #10. Note that all of subsequent formulas are filtering on the Filtered Low Address array.

=LET(
lkadd,G9,
lowadds,ENTIRE_CENTER!$F$1:$F$4376,
highadds,ENTIRE_CENTER!$E$1:$E$4376,
FiltLowAdds,FILTER(lowadds,lowadds<>"",""),
FiltHighAdds,FILTER(highadds,lowadds<>"",""),
lkname,I9,
stnames,ENTIRE_CENTER!$J$1:$J$4376,
FiltStNames,FILTER(stnames,lowadds<>"",""),

nmrows,COUNT(FiltLowAdds),
rowmatch,SUM((lkzip=zips)*(lkadd>=FiltLowAdds)*(lkadd<=FiltHighAdds)*(lkname=FiltStNames)*(SEQUENCE(nmrows))),IF(rowmatch=0,"no match","match at row " & rowmatch))

I did this just by typing, and not in a workbook. You can update the formula step by step by inserting one name and corresponding formula at a time and changing out the last argument (usually 'rowmatch' for the new one you've entered i.e. FiltLowAdds). (The last argument of the LET function is sort of like the "print/display this" argument.


if your concerned with the ENTIRE_CENTER sheet getting longer than 4376 rows then use $F$1$F$10000 (any reasonable upper limit you're certain you won't exceed). If you have just a few lookups you could just drop the numbers in the range reference and use $F:$F. But, that can degrade your performance when you have a lot of calculations going on (which I think you probably do).

 

[rcrumbliss]

Awoohah, thanks for all your help. things are changing constantly as I build this. I made some changes in how the data is being pulled into the file by using Power Query. Doing that i can single out addresses that have a single point range, and no longer need to check the High/Low values, I can do the same by only checking the low value. This narrows down the scope of what I am searching to only 2 variables, Street name and Street number.

I figured out the issue I was having with the variable number of rows by using INDIRECT and OFFSET to pull the row count.

Here is the working code as of now.

=LET(lkadd,G2,lowadds,INDIRECT("ENTIRE_CENTER!$F$1:$F$" &ROW(OFFSET(ENTIRE_CENTER!$F$1,COUNTA(ENTIRE_CENTER!$F:$F)-1,0))),lkname,I2,stnames,INDIRECT("ENTIRE_CENTER!$J$1:$J$"&ROW(OFFSET(ENTIRE_CENTER!$F$1,COUNTA(ENTIRE_CENTER!$F:$F)-1,0))),rowmatch,SUM((TEXT(lkadd,"0")=lowadds)*(lkname=stnames)*(SEQUENCE(COUNTIF(lowadds,"?*")))),IF(rowmatch=0,"no match",  rowmatch))

I have one final problem that I am struggling to figure out. If I have an address with multiple different businesses at a single address, and they show up in my raw data, then I get row numbers outside of the range of rows. What is happening is the SUM formula is counting each row that contains the data. Is there any way to fix that? some way to only count the single row? Unfortunately I dont have any reliable type of data to compare further.

Again, thank you for your help, you have taught me a lot and I appreciate it greatly.

 

[rcrumbliss]

I couldnt edit the previous post, so I am adding. As I was drilling down on the data more, I realized the solution to the above problem was not what I thought it was. The issue lies in when a single address is listed with different zip codes. It was not because of different businesses in one. The fix was simply to add in the zip code, so now I am searching 3 fields. Which works great. Here is the final code, and the solution to all my problems.

Thank you Ahwoohaw for your help.

=IF(A2<>"",LET(lkzip,C2,zips,INDIRECT("ENTIRE_CENTER!$O$1:$O$" &ROW(OFFSET(ENTIRE_CENTER!$F$1,COUNTA(ENTIRE_CENTER!$F:$F)-1,0))),lkadd,G2,lowadds,INDIRECT("ENTIRE_CENTER!$F$1:$F$" &ROW(OFFSET(ENTIRE_CENTER!$F$1,COUNTA(ENTIRE_CENTER!$F:$F)-1,0))),lkname,I2,stnames,INDIRECT("ENTIRE_CENTER!$J$1:$J$"&ROW(OFFSET(ENTIRE_CENTER!$F$1,COUNTA(ENTIRE_CENTER!$F:$F)-1,0))),rowmatch,SUM((TEXT(lkzip,"0")=TEXT(zips,"0"))*(TEXT(lkadd,"0")=lowadds)*(lkname=stnames)*(SEQUENCE(COUNTIF(lowadds,"?*")))),IF(rowmatch=0,"no match",  rowmatch)),"")