?
Solved

Microsoft Excel Filtering

Posted on 2011-05-02
11
Medium Priority
?
277 Views
Last Modified: 2012-05-11
I have an Excel spreadsheet containing multiple columns, one of which has true/false values. Is it possible to create a new sheet and to automatically pull all columns and rows from the primary sheet with a value of "True"?
0
Comment
Question by:rdracer58
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35507749
Can you provide a sample showing exactly what you mean, both in terms of input data and expected results.
0
 

Author Comment

by:rdracer58
ID: 35507769
Input data is contained on the first sheet of the document. It is comprised of a handful of columns, including such items as an ID number, account manager, etc. and the "True/False" column. I would like the second sheet of the spreadsheet to be auto populated with the exact same columns as the first sheet and exact same data, except filtered to show only the "False" columns. If possible, I would like the second sheet to be auto updated when changes are made to the first sheet.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35507771
Why don't you autofilter your sheet and select only the true values and copy them into another sheet? It's the easier way of doing this!

jppinto
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:rdracer58
ID: 35507776
That's what I am doing currently. I was curious if there's an automated way to do that.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35507778
Ah...you want that second sheet to be auto-populated? You can do that using a Pivot Table for instance.
0
 

Author Comment

by:rdracer58
ID: 35507789
Yes, that's correct - I would like the second sheet to be auto-populated. Where can I find information on how to use a Pivot Table to accomplish this?
0
 
LVL 33

Accepted Solution

by:
jppinto earned 500 total points
ID: 35507890
I've attached a sample file where you can see how it's done. I've created a Pivot Table from the first sheet into the second sheet where I filtered the date using the column Active selecting only the "True" values. This will give me only the actual list of the "True" rows. This list is automatically update (the Pivot Table), using this code:

Private Sub Worksheet_Activate()
    Sheets("Filtered").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

This will update the Pivot Table when you select the sheet "Filtered".

jppinto
Filtered-List.xlsm
0
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 1000 total points
ID: 35510535
You can have a macro do that, if you want we can give it a go
For example would you be happy with a macro that finds all the true rows (lets assume the true value is in column E) and copies them to a separate sheet?

If you want, please specify which column contains the TRUE values and the macro can be specific

LMK
0
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 35512267
Within a Macro you could use the Advanced Filter function and this could copy the required data to the second sheet.  It wouldn't be automatic as such as the macro would have to be run to to do the update but that could set to be run on selection of the second sheet for example.

A similar question I answered:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24615743.html

Cheers
Rob H
0
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 1000 total points
ID: 35513943
and without the macro, you can actually use the Advanced Filter options to COPY all records that MATCH a condition (ie your TRUE condition) to a different location (ie within the spreadsheet, in a different spreadsheet).
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 35515034
Advanced Filter done manually will only allow copy to another location on the same sheet, unless that has changed in 2007 and later.

Cheers
Rob H
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question