common value(s) 1

[OCM]

Greetings,

I have a list of students from various states who attended local colleges. What is the best way to find out common college(s) to all students?
e.g. in the attached sample, college #1 and college #3 are common to all students.

Any ideas?

TIA


OCM

 

[SkipVought]

Hi,

Does it matter that your table has duplicates?

Anyhow, use a PivotTable with ID's in ROW and College Name in COLUMN and Count of College Name.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OCM]

Skip,

Thanks for the reply post. The pivot table defaults to Grand total and I couldn’t change value to count (screenshot attached).

TIA,


OCM

 

[SkipVought]

The pivot table defaults to nothing.

When you DRAG College Name into the Values quadrant of PivotTable Fields, if you get anything except Count of College Name, then there's a pop-up handle on that icon where you can select Count of College Name in Value Field Settings.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OCM]

Skip,

Exactly what I expected to see: colleges that are common to all students are college #1 and College #2 (attached screenshot)

Happy New Year!

Thanks again,

Regards,


OCM