Return Value Based on Two Criteria, Filtered by Third

JSTallings

New Member
Joined
Sep 6, 2010
Messages
2
I have a table with the following table:
Student_NameTransferCommunity_College
Jane DoeYesNo
Jane DoeNoYes
John DoeNoNo
John DoeNoNo

<tbody>
</tbody>









Is it possible to add a fourth column that returns a "Yes" or "No" for each unique Jane and John if there is ever a "Yes" in both columns? I would ultimately like to filter a unique list of those students who have transferred.

Student_Name
TransferCommunity_College
Transfer
Jane DoeYesNoYes
Jane DoeNoYesYes
John DoeNoNoNo
John DoeNoNoNo
BillNoYesNo
BillNoYesNo

<tbody>
</tbody>











Longer question:
I am pulling information from a large dataset on our graduates. Every time a student drops out, transfers, or graduates, another row is added with ID#, name, dates, etc. The schools are numbered 1, 2, 3, based on the sequence of the student's enrollment. I am using Power Query successfully to append new data, but obviously nothing is normalized.

The "Yes" and "No" come from a calculated column in PowerPivot that simply returns a "Yes" in column C if a student started in a community college (it is a community college and numbered "1") and a "Yes" in column B if they transferred to a university (it is a university and numbered >1). The larger issue is that each student may have over 14 rows as they move through their degrees.

Can anyone help me in my ignorance? I have a related, normalized table with the students' names, ID numbers and such; since the transfer only happens once, maybe that is a good place for the information rather than repeating it all over the place?

Thank you!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Welcome to MrExcel,

For your first question, this is one formula that should work...
=IF(AND(COUNTIFS([Student_Name],A2,[Transfer],"Yes"),COUNTIFS([Student_Name],A2,[Community_College],"Yes")),"Yes","No")

For your longer question, I'm not sure what you are asking. It would be more efficient to store a value for Transfer and Community College in a normalized table, instead of calculating that value redundantly from your entire dataset. The best approach would depend on considerations like how you would populate that value in your normalized table and whether adding that column to the normalized table is consistent with how your data is organized.
 
Upvote 0
Hey! Thanks very much. That worked perfectly!

For the longer question:
I basically want to do the above, but with a DAX function in PowerPivot so I don't need to build a table in Excel.

Obviously I cannot use cell references, and I know the query will be contextual, so the results will change if I filter either of the three columns.

I think I need to return either an array or a table and run my filters against that, but I'm not sure of the right DAX function.

I have tried the following, but it does not return anything. No errors, just blank.

=CALCULATE(COUNTA(fStudentProgress[Student_Name]), FILTER(fStudentProgress, fStudentProgress[Community_College]="Yes"), FILTER(fStudentProgress, fStudentProgress[Transfer]="Yes")

I appreciate your help.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top