Creating unique sorted list from dynamic table - Formula Solution Still Required

MOD EDIT: The OP has a VBA solution below, but is also looking for a formula alternative.

Hi,

i asked earlier about creating formula to get sorted and unique list here:

https://www.excelforum.com/excel-gen...ml#post4955031

Problem is that i have to create new separated column for this.

What about VBA?

Can we do it on the fly?

I mean to get sorted and unique data validation list.

The best option would be a function which would run when arrow down from data validation list is clicked

Something like: check whata data source it is, and sort it and remove duplicates - in the end show results to the end user.

IT is possible?

Best,
Jacek


Edit: to have 2 solutions in one topic as AliGW said : i need here also second solution: have this done by formulas. Please help.

It is certainly possible but, as I said before, if the list might be longer than 255 characters as a comma separated list (including the commas), you'd still need a separate column for the data; or you'd have to use a control rather than data validation.

Here is a function to get a unique list from a range:

Please Login or Register  to view this content.

That will return an array, which you can sort in numerous ways - e.g. a bubble sort:

Please Login or Register  to view this content.

Once you have the sorted array, you can either drop it into a range, or assign it to the Formula property of the data validation using Join to add the necessary comma delimiters.

Stop complaining and be patient! I have explained the rules to you. This is ONE topic to which you want TWO solutions, not two different topics.

I will now move the thread to the formulas and functions area for visibility.

Since you have 2016, get and transform would be more efficient.

I mean load the table into power query, select the column then sort and group it and output to a new table. Then all you need to do is refresh the query if the source data changes.

Define "not working".

Attach a workbook that shows the problem.

If you can wait a bit, the new dynamic arrays will make this really easy! https://www.microsoft.com/en-us/micr...day-at-ignite/

Array formula in sheet 2, A2, coped down.

=IFERROR(INDEX(List,MATCH(0,COUNTIF(List,"<"&List)+10^10*(List="")-SUM(COUNTIF(List,A$1:A1)),)),"")

Your Named ranges both use OFFSET, which might cause performance issues on REALLY big sheets. is that likely to be an issue?

Replacement for the 2 NRs:

=$A$2:INDEX($A$2:$A$12,SUMPRODUCT(--(LEN(Sheet1!$A$2:$A$12)>0)))

and

=Sheet1!$A$1:INDEX(Sheet1!$A$1:$A$100,COUNTA(Sheet1!$A$1:$A$100))

see sheet. Adjust ranges as needed... but don't go daft and use 000s of rows if you don't need to.

Correct file attached...

You got the phrasing wong...

It was:

+10^10*(List="")

It was an added "extra" which you might not need. If your range contains blanks, this effectively removes them from consideration. If you range CAN NEVER contain blanks, then leave this phrase out and use:

IFERROR(INDEX(List,MATCH(0,COUNTIF(List,"<"&List)-SUM(COUNTIF(List,A$1:A1)),)),"")

array-entered.... instead.

I changed one of the Named Ranges so that it would work if there are blanks in the data. No point in having a formula that works with blanks.... if the Named range doesn't.

COUNTIF(List,"<"&List)+10^10*(List="")

is responsible for sorting the list and removing blanks.

COUNTIF(List,"<"&List)

resolves to:{1;3;2;0;0;3}

This is the number of values in the list that are alphabetically lower than each value.

So, in this case:
BB
DD
CC
blank
AA
DD

{1;3;2;0;0;3}There is one value lower than BB,
{1;3;2;0;0;3} There are 3 values lower than DD
{1;3;2;0;0;3} There are two values lower than DC....

The lowest value (AA) and the blank both return zero. {1;3;2;0;0;3}

To resolve this, +10^10*(List="") is used. This adds 10^10 (10 to the power of 10... 1 with 10 zeros after it) to all instances where the value in the named range is a blank. So... it resolves to:

{1;3;2;10000000000;0;3}

The MATCH function returns the row of the value = zero. the last phrase in the formula ensures that the next lowest alphabetical value becomes zero on the next line... and so on.

Not quite. The formula looks for cells with length >0. It returns true or false. The -- converts true to 1 and false to 0 and SUMPRODUCT adds them up.