+ Reply to Thread
Results 1 to 23 of 23

Sort and Count Unique List Across Multiple Columns - Student Count

  1. #1
    Registered User
    Join Date
    02-21-2011
    Location
    Australia
    MS-Off Ver
    MS Office 2016 on Windows 10
    Posts
    42

    Sort and Count Unique List Across Multiple Columns - Student Count

    Hi,

    I have 12 classrooms each with 24 desks. I have a excel spreadsheet with the students listed in each class and their desk number. Some desks may be vacant in some classes.

    What I need is a list of unique names from all classes, then a count of the number of classes each person attends.

    Ie. Name & Number of Classes

    If this list could be generated using formulas it would be great.

    Any ideas would be appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-17-2014
    Location
    Uppsala, Sweden
    MS-Off Ver
    2010
    Posts
    43

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    I just looked real quick, I think a pivot table (Insert->Pivot table) is good for this type of list that you want to accomplish. Just a quick tip. I'll take another look after lunch if no one has solved it.

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    I'd be interested to see johansoder's Pivot Table solution for 2007, which would obviously be far more efficient than this, though here's a formula-based solution in any case:

    First go to Name Manager (Formulas tab) and create the following:

    Name: Range1
    Refers to: =$B$2:$M$25

    (Or whatever happens to be the range in question.)

    Name: Arry1
    Refers to: =ROW(INDIRECT("1:"&COLUMNS(Range1)*ROWS(Range1)))

    Name: Arry2
    Refers to: =INT((Arry1-1)/COLUMNS(Range1))

    Name: Arry3
    Refers to: =MOD(Arry1-1,COLUMNS(Range1))

    Exit Name Manager.

    Enter this formula in e.g. B38:

    =SUMPRODUCT((Range1<>"")/COUNTIF(Range1,Range1&""))

    Then enter this array formula** in your first cell of choice, e.g. B40:

    =IF(ROWS($1:1)>$B$38,"",INDEX(T(OFFSET(INDEX(Range1,1,1),Arry2,Arry3,,)),SMALL(IF(FREQUENCY(IF(Range1<>"",MATCH(Range1,T(OFFSET(INDEX(Range1,1,1),Arry2,Arry3,,)),0)),Arry1),Arry1),ROWS($1:1))))

    Copy this formula down until you start to get blanks for the results.

    Then enter this (non-array) formula in C40:

    =IF(B40="","",COUNTIF(Range1,B40))

    Again, copy down as required.

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Got the solution..

    Check the attachment..

    XOR have a look at it..

    Don't forget to click *
    Attached Files Attached Files

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Quote Originally Posted by Vikas_Gautam View Post
    Got the solution..

    Check the attachment..

    XOR have a look at it..

    Don't forget to click *
    Of course, but I didn't use a helper column

    Besides, I've always considered this set-up with MATCH(0,COUNTIF(... to be slightly imperfect: a formula which can't start in row 1??! No thanks!
    Last edited by XOR LX; 08-15-2014 at 06:50 AM.

  6. #6
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Thanks for your view...
    But I don't think so...

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Quote Originally Posted by Vikas_Gautam View Post
    Thanks for your view...
    But I don't think so...
    Fair enough, but personally I don't like someone telling me that I can't put a formula in row 1!!

    Regards

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,528

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    @ Vikas

    XOR is right. He has correctly pointed out the limitation of MATCH(0,COUNTIF(.... as you can not put this formula in row 1.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  9. #9
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Everything is possible ..
    have a look..

    Don't forget to click *
    Attached Files Attached Files

  10. #10
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,528

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Yes that's correct. But in this case you need another helper column.
    I think you took it as a challenge, but you didn't try to understand the logic.

  11. #11
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Quote Originally Posted by Vikas_Gautam View Post
    Everything is possible ..
    have a look..

    Don't forget to click *
    Of course! But you've had to add another helper column so that's possible! And it ends up being rather artificial, if you don't mind me saying.

    I'm all for creativity, and I'd never go so far as to say that single-column solutions are better than those using one or more helper columns (quite the contrary in fact), but the fact is that that solution depends on either contriving some sort of helper column set-up (as you've done here) or accepting that it must begin in row 1.

    Both of those two options are perfectly acceptable: it's just that I (and call me a perfectionist if you want) prefer to find solutions which do not depend on either of those two conditions.

    As I said, I don't like the fact that a solution should have to start in a certain row. And I personally find it satisfying to attempt to formulate solutions without the use of helper columns (unless that makes the formula way more complicated than is necessary, that is).

    Of course, this is just my opinion, but then I wouldn't be surprised if there weren't more Excel users out there who also shared a fondness for the idea of producing single-cell solutions which don't require helper cells - not always practical, and a touch indulgent I agree, but perhaps the satisfaction gained from knowing you've managed to achieve such a solution makes up for these drawbacks.

    At least it does for me.

  12. #12
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    sktneer..
    Actually what I think is..
    there is no problem in using helper column when it makes the job easier to understand..

    and yes I took it as a challenge .. because what I am today is because of my this bad habit..

    Sir have look at XOR's solution... he is correct at his place..

    But I solved this question because I want to make it more easier for OP to make it happen.. himself ...


    Vikas Gautam

  13. #13
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,528

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    No problem Vikas. It is the matter of individual approach nothing else.

  14. #14
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Yeah.. sktneer..
    Thanks for understanding..


    Vikas Gautam

  15. #15
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Quote Originally Posted by sktneer View Post
    No problem Vikas. It is the matter of individual approach nothing else.
    Couldn't agree, more, sktneer. I hope I didn't come across as "pushing" my approach onto anyone else. That's just my preference, that's all...

    Quote Originally Posted by Vikas_Gautam View Post
    ...there is no problem in using helper column when it makes the job easier to understand..
    Not at all, and I'm sure the OP may well prefer your set-up to mine for its comprehensibility (not to mention not having to create all those defined names!).

    Let's leave it at that and celebrate the fact that, as we say over here, there's more than one way to skin a cat!

    Cheers

  16. #16
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,528

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Quote Originally Posted by XOR LX View Post
    Couldn't agree, more, sktneer. I hope I didn't come across as "pushing" my approach onto anyone else. That's just my preference, that's all...
    That's what I said too. Probably you misunderstood.

  17. #17
    Registered User
    Join Date
    02-21-2011
    Location
    Australia
    MS-Off Ver
    MS Office 2016 on Windows 10
    Posts
    42

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Thank you for the responses guys.

    Interesting way around the problem! Both ways!

    I am however having a problem inserting the solution into the 'real data sheet' for the following reason.

    The class names have 'other information' columns in between each of the class lists. I have named a range called 'allstudents' which highlights just the students names within theses columns.

    When using the named range in the formulas, I seem to get errors.

    I will continue to work around the problem.... but very much appreciate you time and effort spent on this.

    Thank you

    I will not marked solved just yet just yet to see if there is a solution to a named range with helper columns in between.

  18. #18
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Quote Originally Posted by BuntyMac View Post
    The class names have 'other information' columns in between each of the class lists.
    Ah. This is quite a difference and perhaps would've been worth mentioning!

    Could we see a version which is identical in layout to your actual sheet, then? Obviously with dummy data in place of real data, if necessary.

    Regards

  19. #19
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    This post has been busy! Started working on this last night when there were NO posts.............so I'll take a swing for the fences and submit this one, too.

    Does this remotely resemble what you're after?

    339148d1408075971-sort-and-count-unique-list-across-multiple-columns-student-count-classcount.xlsx

    In the meantime I'll examine the other solutions, and try to catch up.
    Last edited by FlameRetired; 08-15-2014 at 01:06 PM.

  20. #20
    Registered User
    Join Date
    02-21-2011
    Location
    Australia
    MS-Off Ver
    MS Office 2016 on Windows 10
    Posts
    42

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Hi,

    I have placed an updated spreadsheet that better demonstrates the 'real data' layout.

    The problem is the named range is split by other columns..... causing the formulas not to work.

    I hope I'm clear enough in describing the problem.

    Thank you
    Attached Files Attached Files

  21. #21
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Got the solution..
    Check the attachment ..

    Visit
    https://www.excel-buzz.blogspot.in
    to know how I made that list..
    or
    simply use formula auditing..

    Don't forget to click *
    Attached Files Attached Files
    Last edited by Vikas_Gautam; 08-17-2014 at 07:06 AM.

  22. #22
    Registered User
    Join Date
    02-21-2011
    Location
    Australia
    MS-Off Ver
    MS Office 2016 on Windows 10
    Posts
    42

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Thanks Vikas.... looks great now I just have to dissect it to understand.

    I notice you didn't have to use the named range?

    I am also trying to understand the purpose of your 'My Algorithm'

    Thank you for your time and effort spent on this problem.

  23. #23
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Sort and Count Unique List Across Multiple Columns - Student Count

    Try using Formula auditing .. and
    let me know if you want any kind of help in this regard,

    Vikas Gautam

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Unique count of student ids
    By michelemcgeoy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-25-2014, 11:55 PM
  2. [SOLVED] Count unique data across multiple columns
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-29-2013, 06:45 PM
  3. Count unique values across 2 columns with multiple criteria
    By Skywalker01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2012, 08:04 AM
  4. Replies: 11
    Last Post: 06-09-2011, 03:17 PM
  5. Sort and Count Unique Data in 4 Columns
    By miakayuuki in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2008, 11:22 AM

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