+ Reply to Thread
Results 1 to 4 of 4

Count number of times a value appears in different columns, AND list WHERE they appear?

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    2

    Count number of times a value appears in different columns, AND list WHERE they appear?

    Hello all,

    My first post.
    I wish I knew enough to properly phrase my question - it would no doubt have yielded better search results!

    I have been following this forum periodically, and it has always provided me with invaluable assistance - but I have decided to take the plunge and pop up this question, since if I can be helped - it will save many hours of work each year!

    I work at a University Faculty, and offer support to students.
    I first need to identify those who are struggling in particular subjects, and then begin assisting them.

    What I do currently:

    I have 5 different Excel spreadsheets, obtained from various sources - with the test results of students in those 4 particular subjects.
    I then select all those who obtain >45% in each of the 4 subjects, and start a new spreadsheet with 4 columns, listing the STUDENT NUMBER of the students selected (>45%), in those 4 subjects.

    For example:

    A [PolSci11] B [PolSci12] C [PolSci13] D [PolSci14]

    A1 [Stud #] B1 [Stud #] C1 [Stud #] D1 [Stud #]
    A2 [Stud #] B2 [Stud #] C2 [Stud #] D2 [Stud #]
    A3 [Stud #] B3 [Stud #] C3 [Stud #] D3 [Stud #]
    A4 [Stud #] B4 [Stud #] C4 [Stud #] D4 [Stud #]

    As is hopefully clear, each of the columns list ONE occurrence of a unique Student Number in that specific column, for each of the subjects.
    Furthermore, a unique Stud # might only appear in 1 column (if that student only achieved >45% in one of the 4 subjects) - OR, the same unique Stud # might appear (obviously at different places) in ALL four columns (where a student achieved >45% in all 4 courses).

    I know how to use Countif in order to tally how many times Value X (e.g. Student Number "[12656607]") appears in Range A1:D4.

    But what I'm wondering is - would it be possible to design a Formula / arrange the data, so as to not only show how many times [12656607] appears in total, but for which subjects?
    Is this a Pivot Table solution?

    In short - some subjects are more important than others. A student who is >45 in PolSci11 might be worse off than a student performing poorly in both Polsci12 & Polsci14, since the former carries much more credits, or vice versa.
    So I need to not only know the frequency that a particular student/student # crops up in the range, but I need to know "where" as well (i.e. for which subjects)...

    Any conceptual suggestions would be greatly appreciated. I have no clue whether I need to start learning Pivot Table functionality to achieve the above, or whether it would be possible to design a formula that would do the same?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,259

    Re: Count number of times a value appears in different columns, AND list WHERE they appear

    As you have a mechanism to count and, I assume, to identify the students that may have issues, I would have thought you could use the same or a similar formula in Conditional Formatting to highlight the students' records in place rather than trying to extract them or add pointers.

    Alternatively, you could just add a helper column and drop the counter in that and then use AutoFilter to filter out students who match a criterion.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Count number of times a value appears in different columns, AND list WHERE they appear

    Quote Originally Posted by TMShucks View Post
    As you have a mechanism to count and, I assume, to identify the students that may have issues, I would have thought you could use the same or a similar formula in Conditional Formatting to highlight the students' records in place rather than trying to extract them or add pointers.

    Alternatively, you could just add a helper column and drop the counter in that and then use AutoFilter to filter out students who match a criterion.

    Regards, TMS
    I have been using conditional formatting over the past few years, but it becomes incredibly tedious. But then again, I've probably been doing it incorrectly, since you appear to suggest that one can link a formula to Conditional Formatting? I will explore that a bit.

    Will also have a look at the Helper Column suggestion.

    Bottom line is - some columns might have as many as 200 student numbers in them, some only as little as 40-50... Highlighting occurrences of a student number across the columns does not present itself, visually, in a very useful manner - if that makes sense...

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,259

    Re: Count number of times a value appears in different columns, AND list WHERE they appear

    If you need more specific advice, you will need to share a sample workbook with some typical data explaining the process for selecting data ... and what you would hope to see.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1