Searching an array for a match

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,041
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.

Code:
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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top