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

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!

Originally Posted by Vikas_Gautam

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

@ 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.

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.

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.

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

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

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.

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

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.

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 *