Dynamic array - unique values from multiple columns

I am trying to use the new dynamic arrays (currently still only available on the office insider releases) to pull a list of sorted unique values from across multiple columns into 1 column. I can not seem to figure it out though.

The scenario: A row represents a project and there can be unlimited rows.

Each "c#" column value is a company who has requested info for that project. The names are not entered in any particular order.

Ultimately I will be using the unique column as data validation for the "c#" values to choose from. This way, as a unique value which is not already present gets added to one of the columns, the data validation column will have that value as an option in the future to help maintain data integrity (correct spelling, formatting, etc.)

The attached workbook has the base data table, an example of the unique values from each "c#" column, an example of those values stacked, an example of the unique values overall, and then the example of how the finals result should look (the unique values sorted). I also have yellow highlighted cell where the solution can go. I am trying to avoid helper columns and only use a single cell formula that uses the new dynamic arrays.

Any help would be appreciated because at this point I think my head hurts from trying to sort through this. I just don't think I know enough other excel formula tricks that would assist in making this possible.

Thanks in advance for any help.

Chris

Who's on first? LOL

I'm baffled, unclear and

Just guessing. Am I to understand you want to go directly from Base data (Table1) to the unique and sorted DV list in column T (ie without helpers)?

If so what is the yellow highlighted Solution? in cell I14 for?

PS I am not an office insider ... just a subscription user. What I see at my end in your formulas has function place holders and I have no idea what the new functions do and their syntax is a mystery.

Formula:

Please Login or Register  to view this content.

If the formula must include those new functions the available help at this end is likely limited for a while. If it can be done without those is that acceptable?

I don't have the new array functions.

This is just for fun.

The 1st method with the helper column at V2 copy down

Formula:

Please Login or Register  to view this content.

Sort Unique at W2

Formula:

Please Login or Register  to view this content.

2nd method without a helper column.

Y2 Press Ctrl+Shift+Enter

=IFERROR(LEFT($B$3)&SMALL(IF(FREQUENCY(IFERROR(--MID(Table1,2,5),""),ROW(INDIRECT("1:99"))),ROW(INDIRECT("1:99"))),ROWS(Y$2:Y2)),"")

Originally Posted by FlameRetired

Just guessing. Am I to understand you want to go directly from Base data (Table1) to the unique and sorted DV list in column T (ie without helpers)?

If so what is the yellow highlighted Solution? in cell I14 for?

You are correct in that the unique and sorted list is what i want to go to directly from Table 1 without helpers. The green highlighted was an example of the final data goal. The yellow solution cell was to give a spot for the single cell solution to be placed in without altering the final goal example. I like to do this when trying to work through issues to have a known result to check against.

Originally Posted by FlameRetired

PS I am not an office insider ... just a subscription user. What I see at my end in your formulas has function place holders and I have no idea what the new functions do and their syntax is a mystery.

Formula:

Please Login or Register  to view this content.

If the formula must include those new functions the available help at this end is likely limited for a while. If it can be done without those is that acceptable?

That syntax you see is how the versions of excel which do not support dynamic arrays yet show the result of a dynamic array formula. Unfortunatley I am trying to make this happen with only the new formulas. I am doing this to prepare for when they release to update my current system. I am trying to be proactive about the coming change (which will hopefully be very soon).

The new dynamic arrays are pretty cool and use the new calc engine which will remove most of the need for CSE formulas. If I'm not mistaken, the big downside however will be that as of right now they do not work in cross book references unless both workbooks are open.

Edit: Add or clarify wording

Bo-Ry,
That 2nd method result is exactly what I am looking for using the traditional CSE formulas. The reason I am trying to use the new formulas is that there is no need to copy the formula down or use {}. The dynamic arrays automatically produce a result that fills down however far is needed. No risk of not copying down far enough and if some goofball goes in and makes a tweak to the formula they dont have to remember to make it array. Seeing your method makes me wonder if I can use parts of it within the new Dynamic Arrays (DA) to get the result.

Like you, i try to solve stuff just for fun sometimes too.

Thanks for the input and ideas!

Hi,

Neither do I have these new functions, though can you clarify why you are using the 2-step process involving SORT(UNIQUE(P2:P21)), i.e. on your already-created 'stacked' list in P2:P21, and not directly SORT(UNIQUE(B3:G7))?

According to the official documentation, UNIQUE works with 2D ranges as well, as does the SORT function.

Regards

XOR LX,

That was my initial attempt but unfortunately it returned the whole table as columns and rows. I was really hoping that was going to work but alas it did not. In addition when that happens it turns blank cells into "0".

Can you post two screenshots of the Evaluate Formula window, one after the first and one after the second click of the 'Evaluate' button, for the formula

SORT(UNIQUE(B3:G7)))

?

Regards

Is this what you are looking for?

Eval 1
SORTUNIQUE1.png

Eval 2
SORTUNIQUE2.png

Eval 3
SORTUNIQUE3.png

The results are the same whether I use table1 as the reference of b3:g7 with the exception of image 2

Thanks a lot.

Perhaps it can accept 2D ranges, yet only return unique data on a row-by-row (or column-by-column) basis.

This may yet be useful, though can you confirm by posting one further screenshot of the Evaluate Formula window after first setting the optional second parameter for the UNIQUE function ([by_col]) to TRUE? This will instruct the function to compare by columns, not rows, and so from your data we might expect to see the two instances of "c14" in the first column reduced to just one instance in the first column of the array generated by UNIQUE.

Regards

I have actually tried every combination of the unique variables and the results always comes out the same.

UNIQUE1_true.png

Is there a way that the ";" in the evaluate dialog representing a new line can be replaced during the function so that it instead only results in a single row of items? Because if it can result in a single row then I can use the transpose function to automatically make it a column. Another possibility is if i do textjoin(";",true,table1) it will return the entire contents in a single cell separated by ";". The problem is then how do I re-separate it within the formula to make each value individual and able to populate its own cell. In google sheets I would just use a split formula and split it by the delimiter then transpose an voila. done.

Try this

=TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",,Table1)," ",REPT(" ",99)),ROW(INDIRECT("1:"&COUNTA(Table1)))*99-98,99))

Originally Posted by istank

I have actually tried every combination of the unique variables and the results always comes out the same.

Attachment 606078

Actually, I was wanting the next step in the evaluation. Could you share it with me?

Regards

An alternative to Bo_Ry's post #13:

FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,Table1)&"</b></a>","//b")

Regards

Hi All,
Curious to know if anyone has found a solution to this problem using the new dynamic function in Excel?
I have a similar situation, for e.g, I have entries in List1 and List2. I would like a unique list combining the unique values from List1 and List2. I have attached a sample table below.

Please note I would like to get the solution using the Dynamic functions only if it is possible, please.


List1 List2 WantedList
A A A
B B B
C G C
D H D
E E
F F
G
H

Thanks heaps in advance!

Originally Posted by yashbheda

Hi All,
Curious to know if anyone has found a solution to this problem using the new dynamic function in Excel?
I have a similar situation, for e.g, I have entries in List1 and List2. I would like a unique list combining the unique values from List1 and List2. I have attached a sample table below.

Please note I would like to get the solution using the Dynamic functions only if it is possible, please.


List1 List2 WantedList
A A A
B B B
C G C
D H D
E E
F F
G
H

Thanks heaps in advance!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread