We help IT Professionals succeed at work.

Excel Filtering

Ben Cheetham
Ben Cheetham asked
on
79 Views
Last Modified: 2016-02-11
Hi EE

I have a spreadsheet with, say, 5 columns with dozens of rows of data,

I want to show specific columns of certain rows of data based on values in one of the columns.  I want to avoid doing this using a macro.

Rumour has it that I can do this using SQL but I'm a novice on that option.

I don't suppose anyone is willing/able to help?

Thanks
Ben
Comment
Watch Question

[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.
Group Finance Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
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?
MouseCaptainHelp Desk Analyst

Commented:
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
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
:)

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.

Author

Commented:
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
MouseCaptainHelp Desk Analyst

Commented:
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
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
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

Sales data for PivotTable
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
MouseCaptainHelp Desk Analyst

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

TJ
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
...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.

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.