Searching an array for a match

[roscoe]

I have a array that looks like this:
UPC, Date,Price
UPC, Date,Price
UPC, Date,Price
...
UPC, Date,Price
UPC, Date,Price
UPC, Date,Price

UPC can have duplicates (tracking prices over time)

The list could be quite long (a few thousand lines?), I'm looking for the fastest way to search the array given a UPC and date range and find pull the price from the match UPC that falls in the date range. I will also record the data to a worksheet, so if that's faster/easier than searching an array, that's an option also. I can sort the array in what ever way makes sense (I figured UPC first, then date but if here's a smarter way I'm listening)

Bottom line, I've never searched an array for a value so not even sure where to start.

Thanks in advance!

 

[Leith Ross]

Hello roscoe,

Unless you need to automate this process, the simplest approach would to filter the list and do a copy and paste to a different sheet.

 

[roscoe]

Sorry, Yes, I need to automate this within VBA code as part of a larger overall task. Not looking to filter the data as that's too hard (for me) to do within VBA

Input: UPC, Date range
Output: Price

Note the data will be sorted by UPC (i.e. one UPC will be a contigious group of data in the array/table) then date. I could brute force it by looping through the data until UPC matches, then looping by date, but I'm thrinking there must be a faster way...If the UPC is the last one in the list, this seems slow to me. Is there a way to "index" the first value and then start my looping there? Or would that actually be any faster?

Thaks

 

[igold]

There are lots of ways to get the UPC and Date that you want to search for into the search, I chose an InputBox. This code assumes that your first UPC,Date,Price row starts in Cell A2.

Sub SrchArray()


    Dim arr, upcdate, values
    Dim i As Long
    Dim upc As String, dt As String
    
    upcdate = InputBox("Enter the UPC and the date separated by a comma")
    values = Split(upcdate, ",")
    upc = values(0)
    dt = values(1)
    
    arr = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = upc Then
            If arr(i, 2) = dt Then
                MsgBox "The price for UPC: " & upc & vbNewLine _
                    & "On date: " & dt & vbNewLine & vbNewLine _
                    & "is: " & Format(arr(i, 3), "Currency")
                    Exit Sub
            End If
        End If
    Next
    MsgBox "Sorry that UPC & date combination can not be found!"
    
End Sub

Obviously I put the output into a message box, you could write the arr(i,3) value wherever you want.

 

[roscoe]

Thanks. That's basically what I ended up doing. I was expecting it to take longer than it actually did so I was looking for elegant...which it turned out I didn't need

 

[igold]

You're welcome.