Excel Filtering

Hi Ben,

Have you looked at the "Autofilter" &/or "Advanced Filter" feature available from the "Data" menu of MS-Excel?

Please see: "Filter a range of data"
[ https://support.office.com/en-ca/article/Filter-a-range-of-data-057936b7-010a-4353-ae68-320a9c60b093 ]

If you would like to provide a sample workbook attached to this question thread, removing any sensitive information in advance, then indicate what you wish to filter upon, this may help the provision of suggestions/further assistance.

Many thanks for the suggestion.  Having re-read my question, I omitted to say that I need this to be dynamic and on another worksheet in another workbook, ideally with the option to change the criteria on the destination worksheet.

I hope you are still available to advise.

Hi again,

No problem.

I do not think you can achieve what you (now) describe without resorting to the use of Visual Basic for Applications ("macro") code to some extent.

Yes, you can use Structured Query Language [SQL] syntax to effectively filter data (by overlaying/replacing the original data set within the other workbook/worksheet with the filtered results based on the criteria selected within the first workbook/worksheet) with the assistance of an underlying database-specific Component Object Model [COM] within the Microsoft Data Access Components [MDAC] sub-system, such as the ActiveX Data Objects database [ADODB] library acting as a middleware layer between the MS-Excel programming environment & the Object Linking & Embedding, Database [OLE DB] Application Programming Interface [API].

! :)

Or, simply, yes, you can do what you describe with SQL, but VBA ("macro") code will still be required.

Applying (Auto)Filtering on another worksheet can also be achieved but, again, utilising interaction via VBA code.

Do you have restrictions on using Visual Basic for Applications within your designated run-time environment?

You can do this using a VLOOKUP with nested MATCH function in the column_index argument.

In the destination sheet, enter which columnd to display.  In the MATCH function, lookup which column this will be.  In the VLOOKUP function, look up the unique identifyer in the original range and return which column it will be (via the MATCH).

let me know if you need more detail
TJ

:)

Sorry... after TJ's reply (& to some extent, Roy_Cox's suggestion/link), I may be over-thinking this led by your initial mention of the possibility to use SQL.

If either of the other two Experts are heading towards your ultimate goal, please pursue these suggestions as required.

If you do need a SQL-based approach, however, please come back to me if/when necessary.

Well, aside from being very impressed with all answers to date, I've tried the "Get External Data" feature and have successfully got a filtered list on new sheets in a separate workbook.

Unfortunately, however, the filter means that the other data is still there but just on hidden rows.  I wanted to use the new, filtered list as lookup values in a list box (forgive my terminology).

So I am stuck again.

Some simple SQL or macros might be okay but I don't really want to have users having to run them.

I like the idea of the lookup or match functions if that can somehow get me the list.

Let me know if a dummy spreadsheet would help explain my intentions better.  I'd just have to remove sensitive data first.

Thanks so far.
Ben

Data | Advanced filter... has the ability to copy the filtered list to a new location (in the workbook).  That way you are eliminating the hidden rows.

Also, you may like the database-like results provided by Get External Data > From other sources > Microsoft Query > Excel files.  It will take you through a wizard to narrow and filter your results.  Then you can edit the external data range to specify which cell (on the destination sheet) contains a parameter value that can be changed easily before refreshing the data.

TJ

Here's a PivotTable created from an external workbook. Takes about five minutes and can be refreshed when required.

Insert Tab  -> PivotTable -> Use an external data source.
Choose connection and browse for the other workbook


Create the PivotTable by dragging the fields from the Wizard to the PivotTable

Right Click on any date and choose Group

There's plenty of Tutorials available for achieving this and requires no VBA

This is Grouped by Month, Qtr and Year
Sales.xlsx
PivotTable---External-Data.xlsx

if you provide the sample workbook, including both a source and desination sheet, I will provide a VLOOKUP/MATCH function for it.

TJ

...and, just for clarity, my proposed suggestion utilising Visual Basic for Applications was based on the premise that the solution would have been fully-automated with no manual intervention other than the initial selection of the criteria.

I think I read more into your question than you intended.  My apologies for confusing the issue so far.

You now have a few options to consider, but I will stay subscribed to the thread in case none of these result in what you were looking for.

Very grateful to all.

I am going to check I can progress this on Monday probably, dare I say it, after a quick go with the pivot table option as first indications suggest that it will work!

Will close the case then hopefully and identify the best solution.

Thanks again.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.