How to make a list of unique values that orders alphatically (or numerically)?

[KINGSOFTUSER]

Good day!

I know how to use this array formula:

={INDEX(ARRAY,MATCH(0,COUNTIF($A$1:A1,ARRAY)}

It works really good, but I would like to have the values retrieved in an alphatical order without ordering the original sheet first so the values update in that order. Can somebody help please?

Thank you!

 

[XOR LX]

Hi,

For an alphabetically sorted listed, and assuming there are no blanks within the range, this array formula** should do what you wish:

=INDEX(ARRAY,MATCH(TRUE,IF(FREQUENCY(MATCH(ARRAY,ARRAY,0),MATCH(ARRAY,ARRAY,0)),COUNTIF(ARRAY,"<"&ARRAY))=SMALL(IF(FREQUENCY(MATCH(ARRAY,ARRAY,0),MATCH(ARRAY,ARRAY,0)),COUNTIF(ARRAY,"<"&ARRAY)),ROWS($1:1)),0))

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

 

[KINGSOFTUSER]

Thank you very much, but what if there are blanks? Sometimes I name entire ranges of cells, so I don't have to update the formula. Like select the whole column and name it, I would use that as an array (and there are plenty of blanks there heh).

 

[XOR LX]

In that case, I can't personally come up with a better version than this:

Create a new Named Range, ARRAY2 say, as:

=T(OFFSET(INDEX(ARRAY,1,1),SMALL(IF(ARRAY<>"",ROW(ARRAY)-MIN(ROW(ARRAY))),ROW(INDIRECT("1:"&SUMPRODUCT(--(ARRAY<>""))))),,,))

The (array) formula for returning unique, alphabetically-sorted entries from your original list (presumed stored as Named Range ARRAY) is now:

=INDEX(ARRAY2,MATCH(TRUE,IF(FREQUENCY(MATCH(ARRAY2,ARRAY2,0),MATCH(ARRAY2,ARRAY2,0)),MMULT((--(ARRAY2>TRANSPOSE(ARRAY2))),ROW(INDIRECT("1:"&COUNTA(ARRAY2)))^0)=SMALL(IF(FREQUENCY(MATCH(ARRAY2,ARRAY2,0),MATCH(ARRAY2,ARRAY2,0)),MMULT((--(ARRAY2>TRANSPOSE(ARRAY2))),ROW(INDIRECT("1:"&COUNTA(ARRAY2)))^0)),ROWS($1:1))),0))

Again, copy down as required.

Regards

 

[KINGSOFTUSER]

Thank you! I don't fully understand...

If I have the say A:A range, should I name it ARRAY2 and copy that formula "T(OFFSET..." in every cell and in another cell copy down the second formula "=INDEX(ARRAY2..." having the original range contaning the data, say, B:B named "ARRAY"?

 

[XOR LX]

No. I'm assuming your original range containing blanks is stored as a Named Range called ARRAY. I wouldn't recommend that you use an entire column here though, i.e. A:A, since this can have negative effects upon calculation speed.

If this range is dynamic, i.e. may change in size, and you don't want to have to continually redefine it, then either use a fixed range which will be sufficiently large to cover all likelihoods, e.g. $A$1:$A$1000 (I would also recommend using absolute references - the "dollar signs" - here) or, even better, we can offset it so that it is dynamically and automatically redefined by Excel depending on how many entries are in that column, e.g. define ARRAY in Name Manager as:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("zzz",Sheet1!$A:$A))

(Obviously amend the sheet name (Sheet1) and starting cell ($A$1) as required.)

ARRAY2 is another Named Range to be stored precisely as I gave it in my last post. You then paste the final formula I gave into the first cell in which you wish to start your list, making sure you commit it as an array formula, and then copy it down as far as required.

You'll get #N/As when there are no more unique returns to be given. If you would rather not have these, we can add an error trap to the formula to hide them:

=IFERROR(INDEX(ARRAY2,MATCH(TRUE,IF(FREQUENCY(MATCH(ARRAY2,ARRAY2,0),MATCH(ARRAY2,ARRAY2,0)),MMULT((--(ARRAY2>TRANSPOSE(ARRAY2))),ROW(INDIRECT("1:"&COUNTA(ARRAY2)))^0)=SMALL(IF(FREQUENCY(MATCH(ARRAY2,ARRAY2,0),MATCH(ARRAY2,ARRAY2,0)),MMULT((--(ARRAY2>TRANSPOSE(ARRAY2))),ROW(INDIRECT("1:"&COUNTA(ARRAY2)))^0)),ROWS($1:1))),0)),"")

Regards

 

[KINGSOFTUSER]

Well thanks for your kind reponse. But, sorry for my insistence, could you please upload an example file? (last formula gives me an error, and I don't know how to make the other ones >.<).

Cheers.

 

[hiker95]

KINGSOFTUSER,

could you please upload an example file?

You are the one asking for help.

You should supply a screenshot of what your raw data looks like, and, a screenshot of what the results should look like.

Or, you can give us your workbook.


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.