Formula to return unique values in a range in alphabetical order - explanation needed.

[tmk221]

Hi Everyone,

Assume we have a list of letters/words in A2:A11 and we want to show in B2 and down all unique values from A2:A11 in alphabetic order. This can be achieved using in B2 this array-entered formula: =INDEX($A$2:$A$11,MATCH(0,COUNTIF($A$2:$A$11,"<"&$A$2:$A$11)-SUM(COUNTIF($A$2:$A$11,"="&B$1:B1)),0))

I tried to figure out how this formula works but I couldn't. I understand this part only COUNTIF($A$2:$A$11,"<"&$A$2:$A$11). COUNTIF($A$2:$A$11,"="&B$1:B1)) seems to be a circular reference to me and I have no idea how to crack this formula from here...

I managed to write a formula that will do A-Z sorting but listing all values, duplicates will be included - arrayed entered in:
=INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),ROW()-1),COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),0))

and I manged to write a formula that will return unique values in alphabetical order, but using one extra helping column:
helping formula in D2: =IF(COUNTIF(A2:$A$11,A2)>1,"x",COUNTIF($A$2:$A$11,"<"&$A$2:$A$11))
final formula - array entered: =INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),ROW()-1),$D$2:$D$11,0))

If anyone could help me with the folloing question that would be great:

1) Please explain step by step how the original formula works

2) I wrote a formula that will return exactly the same result as original formula, but I need to use a helping column. Why nesting the helping coulmn into the final formula doesn't work the same way as keeping both formulas separate? The final formula will look like that - array entered: =INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),ROW()-1),IF(COUNTIF(A2:$A$11,A2)>1,"x",COUNTIF($A$2:$A$11,"<"&$A$2:$A$11))))
And it almost works, except it returns error for duplicated values. Any idea how to fix it?

3)How come COUNTIF($A$2:$A$11,"<"&$A$2:$A$11) expression entered in let's say H2:H11 returns different values for different rows? The formula looks exactly the same in each cell, all references are absolute.

Thanks
tmk221

[JBeaucaire]

Your first formula works good. Click on the cell B2, then go to Formulas > Evaluate Formula, step through the formula one calc at a time, very educational.

Notice the MATCH is looking for 0 in the array of row numbers it creates, 0 will be the position of the first string that is greater than the previous. Since there are none previous because this is the first time it's used, it will find the "a".

Now click on B3 and run it again. Notice the COUNTIF counts how many matches have occured prior and substracts that from the array its building? This moves the position of 0 around and wherever it is found that is where that match will occur for that cell.

Keep clicking on other cells and using the Evaluate Formula function, after 5-6 times through it should be clear what it's doing.

[tmk221]

Thanks JBeaucaire I get it now, I ran 'Evaluate Formula' this morning and everything became clear.

Still could anyone explain to me why does COUNTIF($A$2:$A$11,"<"&$A$2:$A$11) entered in B2:B11 return different values in different cells? The formula looks exactly the same in each cell!? And it's not array entered, which confuses me even more...

And I just noticed that the formula does not work if you have numbers in your array. Is there an easy fix for that?

[Marcol]

Here's another tip.

Select the formula you want to evaluate.
Then in the formula bar, select the part of the formula you want to see the result of and press f9

Use undo to return to the full formula.

This is similar to using the fx dialogue box, but it doesn't "split" the formula, it reads as one line.

[tmk221]

Thanks Marcol! That is one hell of a tip! I spent plenty of time splitting the formula into separate columns to see what happens step by step, now I can do that with one click.

[JBeaucaire]

...explain to me why does COUNTIF($A$2:$A$11,"<"&$A$2:$A$11) entered in B2:B11 return different values in different cells? The formula looks exactly the same in each cell!? And it's not array entered, which confuses me even more...

Yes, the FIRST time you see that it is the same and when you run the Evaluate you see that it creates the same initial array. But the second time it occurs, there is a "test" attached...
IF(COUNTIF(A2:$A$11,A2)>1,"x",COUNTIF($A$2:$A$11,"<"&$A$2:$A$11))

So, not only is the range at the beginning changing now, there is a test, and the items in the first array are being replaced by "x", which means the when original array is "adjusted" by the values created in this second array, the adjustments are different each time. Keep evaluating, you'll see.

[tmk221]

I understand that it is used that way so that it creates exactly the same array of values, then we substract the result of sum(countif(...)), so that MATCH have unique array to look in for 0 every time.

But what I meant to ask is why does standalone and not array entered COUNTIF($A$2:$A$11,"<"&$A$2:$A$11) returns different values in different rows. Is it that excel automatically checks the relative position of the formula compared to the absolute range that was used, and reads that absolute range as single cell reference? Basically what I don't understand is why that formula works the same as this one: COUNTIF($A$2:$A$11,"<"&A2).

BTW, any ideas how to adjust the formula so that it tolerates numeric values as well? I'll try to solve it on my own this evening. I'll update the thread in case of success

[JBeaucaire]

These are variations on the same theme. Here are the formula snippets I use as the basis of starting my list of ordered unique values from a range with duplicates. The first formula gives you the first value, then the second formula is copied down to get all the proceeding values.


'DYNAMIC LIST OF UNIQUE VALUE FROM A COLUMN WITH DUPLICATES (array formulas)
1st cell =INDEX(A$1:A$100,MATCH(0,COUNTIF(A$1:A$100,"<"&A$1:A$100),0))
or simply =A1 if you know the address of the first item

2nd cell =IF(COUNTIF(A$1:A$100,">"&B1), INDEX(A$1:A$100, MATCH(COUNTIF(A$1:A$100,"<="&B1), COUNTIF(A$1:A$100,"<"&A$1:A$100),0)),"")
(copy down as far as needed)

These are array formulas. I use this for numerics as well as text,but not BOTH. IF a single text value is in the range, it will not see any of the numerics.

[tmk221]

First of all your avatar hurts my eyes I had use adblock on you

Is there a way to concatenate the whole range with some value i.e.: "a"? That would solve the problem with numeric values as all values in the array would be text. As workaround helper formula could be used to concatenate A2:A11 with some letter. On the other hand I can't imagine a real life situation when this would be needed.

[JBeaucaire]

no idea what you mean. Post an example and I'll see if I can make it work.

[tmk221]

The problem seems to be that COUNTIF($A$2:$A$11,"<"&$A$2:$A$11) assigns "0" to all numeric values and first text value. So if we could trick somehow this function to use "a"&($A$2:$A$11) range that would solve the problem.

Using helper formula we could put in B2 and down ="a"&A2. and then we could use =MID("original formula",2,256). This way we have unique values only and they are sorted in alphabetical order, and it doesn't matter if they are numeric or text.