How to filter out records and dependency on other column

whoiswct

New member
Joined
Nov 24, 2012
Messages
57
Reaction score
0
Points
0
How to filter out records and dependency on other column?
I have attached a sample data to elaborate my problem.

Assumption: the source data cannot be modified.
I want to show the FAMILY which have at least 1 "F" in SEX column.
 

Attachments

  • excel2010_sample_filter_depend_on_other_field.xlsx
    12.7 KB · Views: 49

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Add a column to the data with a header of say Test and a formula of

=COUNTIFS($A$1:$A$14,A2,$C$1:$C$14,"F")>0

Then add Test to report filter on the pivot and filter for True
 

whoiswct

New member
Joined
Nov 24, 2012
Messages
57
Reaction score
0
Points
0
Add a column to the data with a header of say Test and a formula of

=COUNTIFS($A$1:$A$14,A2,$C$1:$C$14,"F")>0

Then add Test to report filter on the pivot and filter for True
Sorry to inform you that I cannot modified the data source.
I connect as external data source since it is modifying regularly so that I will not copy to local.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Sorry to inform you that I cannot modified the data source.

So why not retrieve it to an Excel table. It will still update for you when you open the workbook. Base your PivotTable off the table in the workbook, and you can do what you like to that data source...
 

whoiswct

New member
Joined
Nov 24, 2012
Messages
57
Reaction score
0
Points
0
So why not retrieve it to an Excel table. It will still update for you when you open the workbook. Base your PivotTable off the table in the workbook, and you can do what you like to that data source...
If I retrieve to an excel table, it will become a offline data only but not a live data.
The source excel data is modifying every day.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
So you retrieve it every day. That is an easily automated task.

What version of Excel are you on?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
The I suggest that you use PowerPivot to query the data source and manage it there.
 
Top