Filter based on cell value

[Sumilidon]

Morning all,
I am hitting a bit of a wall with regards to a recent project and hoping you could help.
In short, I have a database of over 500,000 rows that I want to search based upon multiple criteria. I tried using a Countif solution which works, but it’s so resource heavy that the end users just crash their tiny PCs.



I was therefore hoping to use a filter solution



Sheet 1 contains the data to be searched:
Column A contains a unique reference number
Column E contains a Date of Birth
Column I contains a Postcode



Sheet 2 contains the input data the end user wants searches
Column A is where they add any references numbers they have (up to 20)
Column B is where they add any DOBs they have (up to 20)
Column C is where they add any Postcodes they have (up to 20)



I want it so that on Sheet 2, the end user can type in any references they may have into 1 column, any dates of birth in the second and any postcodes in the third. They press a button, a Macro runs and applies a Filter on Sheet 1 for the above.



To make matters more complex, I want it to return any rows with at least 1 match and for the Unique Reference and Postcode filters to be a "contains" rather than exact match. This means it must also include blanks



Normally I would just record a macro and manually do it the first time, but it won’t allow me to select a cell reference and as said, I used the Countif method which works excellently – but because of the file size is far too resource heavy.
Any help appreciated

 

[Col Delane]

You could consider using the Advanced Filter functionality (not part of the Autofilter criteria but a separate feature under the Data / Sort & Filter menu)

1. You would want to select "Filter the list, in place" as the Action
2. The database in Sheet1 would be your List Range
3. Sheet 2 is where you'd locate the Criteria Range, which would need:
   • 3 columns for the search criteria (Ref, DOB, Postcode) with exactly the same column headings as on Sheet1, and
   • 60 rows because I'm assuming you want to see all rows where the Ref. No, DOB or Postcode match (rather than combinations of Ref and DOB and Postcode) The values in these "fields" can also use wildcards for your "contains" searches.
4. It's a while since I've run an Advanced Filter, but I remember that when the List Range and Criteria Range are on different sheets, you need to select one of them first (but can't recall which one, sorry).
5. You could record a macro when you have the process sorted, and link it to a button which the the users could click once they've entered their search data.