+ Reply to Thread
Results 1 to 15 of 15

Dynamic array - unique values from multiple columns

  1. #1
    Registered User
    Join Date
    06-12-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    365
    Posts
    16

    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
    Attached Files Attached Files
    Try, fail/succeed, and learn.

  2. #2
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,371

    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: copy to clipboard
    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?
    Last edited by FlameRetired; 01-13-2019 at 12:29 AM. Reason: after thoughts
    Dave

  3. #3
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,319

    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: copy to clipboard
    Please Login or Register  to view this content.



    Sort Unique at W2
    Formula: copy to clipboard
    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)),"")
    Attached Files Attached Files
    Bo

  4. #4
    Registered User
    Join Date
    06-12-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    365
    Posts
    16

    Re: Dynamic array - unique values from multiple columns

    Quote Originally Posted by FlameRetired View Post

    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.

    Quote Originally Posted by FlameRetired View Post
    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: copy to clipboard
    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
    Last edited by istank; 01-14-2019 at 10:16 AM.

  5. #5
    Registered User
    Join Date
    06-12-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    365
    Posts
    16

    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. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,716

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Registered User
    Join Date
    06-12-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    365
    Posts
    16

    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".
    Last edited by istank; 01-14-2019 at 11:16 AM.

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,716

    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. #9
    Registered User
    Join Date
    06-12-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    365
    Posts
    16

    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
    Last edited by istank; 01-14-2019 at 11:41 AM.

  10. #10
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,716

    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. #11
    Registered User
    Join Date
    06-12-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    365
    Posts
    16

    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. #12
    Registered User
    Join Date
    06-12-2017
    Location
    Pittsburgh, PA
    MS-Off Ver
    365
    Posts
    16

    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. #13
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel2016
    Posts
    1,319

    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. #14
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,716

    Re: Dynamic array - unique values from multiple columns

    Quote Originally Posted by istank View Post
    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. #15
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,716

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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