Excel Filtering

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
Ben CheethamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
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.
0
Ben CheethamAuthor 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.
0
Roy CoxGroup Finance ManagerCommented:
A PivotTable may be an option
Connect Data to another workbook
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

[ fanpages ]IT Services ConsultantCommented:
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?
0
MouseCaptainHelp Desk AnalystCommented:
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
0
[ fanpages ]IT Services ConsultantCommented:
:)

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.
0
Ben CheethamAuthor 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
0
MouseCaptainHelp Desk AnalystCommented:
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
0
Roy CoxGroup Finance ManagerCommented:
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
1
MouseCaptainHelp Desk AnalystCommented:
if you provide the sample workbook, including both a source and desination sheet, I will provide a VLOOKUP/MATCH function for it.

TJ
0
[ fanpages ]IT Services ConsultantCommented:
...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.
0
Ben CheethamAuthor 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.
1
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.