Create a sorted list of uniques based on 2 columns

[Opsintern]

Hi all,

I have managed to come up with a formula which extracts a list of uniques values(text), which is the following formula (where CommData[Driver] is List1 and PaxData[Driver] is List2):


=IFERROR(IFERROR(INDEX(CommData[Driver],MATCH(0,COUNTIF($B$4:B4,CommData[Driver]),0)),INDEX(PaxData[Driver],MATCH(0,COUNTIF($B$4:B4,PaxData[Driver]),0))),"")

Now this formula just returns the unique values but I'd like them to be returned in a sorted(A-Z) list.

All ideas are welcome!

Thanks :)

[Opsintern]

Anyone?

[Admin]

HI

See: Sorting numbers and text cells also removing blanks using an array formula | Get Digital Help - Microsoft Excel resource (http://www.get-digital-help.com/2009/06/06/sorting-numbers-and-text-cells-also-removing-blanks-using-array-formula-in-excel/)

[Opsintern]

Hi Admin,

Thanks for your reply,
I came across this article, but I couldn't quite figure out how to use this one for 2 lists. Frankly, I don't completely understand the formula in the article and don't see where the formula actually sorts. Also, the formula in the article sorts the complete list, instead of returning unique values.

Any help would be highly appreciated!

Thanks,

[Admin]

I haven't go through the formula yet. I guess this is a very expensive formula, so my question why don't you sort your source range, so that your original formula will return in sorted order?

[Opsintern]

That is indeed correct,

I could sort the source, but since the source is in 2 ranges, it will not return completely sorted. I will look like this:

AA (List 1 Data)
BB
CC
FF
GG
KK
OO
ZZ
AA (where data from list 2 starts being returned)
BB
CC
DD
XX

Anyways, I'm not really in need anymore, since I found a work around. See, the INDEX list created with above formula is returned for the purpose of selecting what I want to analyze in a table, where I use a formula quite like the one in the article you mentioned, which uses the INDEX function to return a sorted list to another location.



But would be nice if I know how to get this to work, I'm pretty curious if it is possible.

Cheers,