Finding Unique Values Across Multiple Columns

[elliott.smith]

Hello Excel Masters!


I am trying to sort through a long list of data (over 21,000 rows) of student profiles in higher education and I need to get specific counts from this data.


Unfortunately, the database outputted the data in a less-than-ideal way. For example, each academic concentration per student occupies a row such that if one student has a double Major concentration, the same student occupies 2 rows. I am able to use the Excel filter to take away the specific academic concentrations (located in a different column) but I need to find how many students have double Major concentrations vs. single Major concentrations.


I recognize this is quite abstract without seeing the data, but due to FERPA laws I'm not able to share the spreadsheet. So my questions, as poignantly as I can put them, are these:

-How can I count the unique numerical values in data set d10:d21712? [Goal: to see how many unique students are in the database]

-If I filter the unique values in d10:d21712, will that filter hold (i.e. not be reset) if I then filter data set e10:e21712 to show me only the students with more than one academic concentration? [Goal: to see how many unique students have multiple concentrations]

-Once I know how many unique students have multiple academic concentrations, can I keep both of the filters above (i.e. so they aren't reset) and then filter the data set q10:q21712 to show me specific major concentrations? [Goal: to see how many unique students have double Major concentrations versus double Minor concentrations]


I would appreciate all input! I've perused the forums many times and can't quite find what works. I've tried the =SUMPRODUCT(1/COUNTIF(d10:d21712,d10:d21712)) formula but I got a #DIV/0! error message (by the way, what does the 1 represent in this formula??).


Help!

 

[Montrey]

Check this link out. Gives you lots of options.


http://www.youtube.com/watch?v=XPb0S3Rbdf4


Let us know if it doesnt work out.

 

[Montrey]

Have you tried a pivot table?

 

[elliott.smith]

Hey Montrey,


Thanks for the video. It really just reinforced what I had tried earlier...


I ended up taking the "outsmart excel" approach and created a new spreadsheet with data from the columns I needed filtered, so as to "lock" the filtered data in place.


Thanks a MILLION for such a speedy response though!


Best,

Elliott

 

[Montrey]

Hey sorry I couldn't help you further. Its sort of tough without a sample file.

Anyways glad you found a solution.


:D

 

[Faseeh]

Hi Montrey,


You can always ask for Sample Sheet with random data Using Rand() & Randbetween() in case some one has confidential data. Once done, he/she can eliminate formula working behind by paste special to get sample data!


Regards,

Faseeh