# Dynamic array - unique values from multiple columns

1. ## 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

2. ## Re: Dynamic array - unique values from multiple columns

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?

3. ## Re: Dynamic array - unique values from multiple columns

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)),"")

4. ## Re: Dynamic array - unique values from multiple columns

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.

5. ## Re: Dynamic array - unique values from multiple columns

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!

6. ## Re: Dynamic array - unique values from multiple columns

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

7. ## Re: Dynamic array - unique values from multiple columns

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".

8. ## Re: Dynamic array - unique values from multiple columns

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

9. ## Re: Dynamic array - unique values from multiple columns

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

10. ## Re: Dynamic array - unique values from multiple columns

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

11. ## Re: Dynamic array - unique values from multiple columns

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

UNIQUE1_true.png

12. ## Re: Dynamic array - unique values from multiple columns

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.

13. ## Re: Dynamic array - unique values from multiple columns

Try this

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

14. ## Re: Dynamic array - unique values from multiple columns

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

15. ## Re: Dynamic array - unique values from multiple columns

An alternative to Bo_Ry's post #13:

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

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1