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
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
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.
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.
I hope you are still available to advise.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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?
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
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.
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
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
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
![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
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
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.
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.
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.