# Sort and Count Unique List Across Multiple Columns - Student Count

1. ## 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

2. ## 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. ## 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).

4. ## 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 *

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

Originally Posted by Vikas_Gautam
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!

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

But I don't think so...

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

Originally Posted by Vikas_Gautam
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. ## 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.

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

Everything is possible ..
have a look..

Don't forget to click *

10. ## 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. ## Re: Sort and Count Unique List Across Multiple Columns - Student Count

Originally Posted by Vikas_Gautam
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. ## 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. ## Re: Sort and Count Unique List Across Multiple Columns - Student Count

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

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

Yeah.. sktneer..
Thanks for understanding..

Vikas Gautam

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

Originally Posted by sktneer
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...

Originally Posted by Vikas_Gautam
...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. ## Re: Sort and Count Unique List Across Multiple Columns - Student Count

Originally Posted by XOR LX
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. ## 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. ## Re: Sort and Count Unique List Across Multiple Columns - Student Count

Originally Posted by BuntyMac
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. ## 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.

20. ## 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

21. ## 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 *

22. ## 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. ## 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

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

#### 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