Need to pull address ranges with accompanying data for large data base

Can you post a sample worksheet that contains your desensitized data?

Yes.

Depending on how much data you think you'll be returning there are a few options.

• If you only intend to pull 10-20 rows per criteria, I'd go the route of INDEX(..SMALL(IF array.
• If you think your criteria might pull a few hundred or thousand rows, you'd probably be better served by a VBA procedure.

If you want to create a mockup with some made up data and attach it here, I can write the formulas and script so you can see them both.

I use the first option for small returns, and a VBA script for a database of 294,000 rows that compares latitude and longitude of each entry to user defined point and populates all rows within X miles.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data. (Make stuff up if you have to; don't use real people's names or include Personally Identifying Information)

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Here's an even simpler solution than the ones I mentioned, and it's easily understood and modified:
Instead, we run an Advanced Filter that pulls the data as if it were in a real database file.

First, we adjust your criteria to fit: numbers ranges should be >= X and <= Y.

From what I have setup so far, go to the Filtered Results tab. An advanced filter of this nature can only be started from the destination tab.

1. Select A1:K5 to make Excel think this is the source data. It won't let you start otherwise.
2. At the top of the screen choose Data, and then click Advanced under 'Sort & Filter.'
3. Under Action choose Copy to Another Location
4. For List Range click the spreadsheet icon and go select the database tab and select all of the data including the headers
5. For Criteria Range select A1:K5 of the results sheet
6. For Copy To select A9:K9 of the results sheet

Hit Okay

This will compare the List Range to the Filter and pull relevant matches. A single record must match all criteria of the first line, or all criteria of the second, etc.

I see. It sets the named ranges when I did it the first time and I was off two columns

Not to be outdone by simplicity, I wrote the VB Code also.
It's good practice.

The code below is already included in the attached workbook.

Please Login or Register  to view this content.

Using the image you provided, you'd first need to make your criteria field names match your actual database.

This means M3 must = H1. N3 = I1. Must be exact match.

The you'd select Results Tab. Click Advanced.

List Range would be 'Street Range'!A1:K54
Criteria Range would be 'Street Range'!M3:N7
CopyTo would be A1

No problem. Glad I could help out.