+ Reply to Thread
Results 1 to 14 of 14

How can I modify my array TEXTJOIN formula to output only unique values

  1. #1
    Registered User
    Join Date
    07-04-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    8

    Question How can I modify my array TEXTJOIN formula to output only unique values

    This is my worksheet:
    Capture1.PNG

    This is the formula in question:
    Please Login or Register  to view this content.
    Untitled1.png

    My goal is to collect all markets in column I that belong to the same Invoice number, into one cell in column J.
    I made this wildcard array TEXTJOIN formula that does everything that I want it to do, exept that it outputs all the values it finds; when I want it to only output the unique values.

    Desired results:
    Capture2.PNG

    How can I modify this formula to accomplish this?
    Attached Files Attached Files
    Last edited by Victorjo; 07-05-2018 at 06:38 AM.

  2. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    Welcome to the forum.
    please attach sample file, as per forum rule https://www.excelforum.com/forum-rul...rum-rules.html
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  3. #3
    Registered User
    Join Date
    07-04-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    8

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    Workbook attached ; ; ; ; ; ; ;

  4. #4
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    Try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Checkout here as well: https://www.ablebits.com/office-addi...-values-excel/

  5. #5
    Registered User
    Join Date
    07-04-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    8

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    That formula is able to output unique values, but the issue is that its reference cells are static and has a constant range lenght. I have found many different formulas online that accomplishes that. The real problem I have is that I can't figure out how to combine a formula like yours which outputs unique values, with a formula like mine which has dynamic cell references that are based on which rows have the same value in column C.
    Last edited by Victorjo; 07-06-2018 at 01:56 AM.

  6. #6
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    Ok, you wanted to have unique values only in row J3 for all smiliar values in col C (WALLENIUS WILHELMSEN LINES AS NORWAY, BRANCH SWEDEN = 22 rows, it may vary)

  7. #7
    Registered User
    Join Date
    07-04-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    8

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    My bad, I meant column E, not C. Othervise you are correct, the invoice has constituent invoices with the same invoice number. Every constituent invoice(each row) has one market, and I want to collect all markets from the invoice's contistituent parts into column J.
    Last edited by Victorjo; 07-04-2018 at 09:01 AM.

  8. #8
    Registered User
    Join Date
    07-04-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    8

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    I also realised that my original formula should trigger like this (On New Invoice Number)
    Please Login or Register  to view this content.
    Instead of this (On New Vendor)
    Please Login or Register  to view this content.
    Last edited by Victorjo; 07-04-2018 at 09:11 AM.

  9. #9
    Registered User
    Join Date
    07-04-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    8

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    You can see in my images that J3 contains all the markets from column I on the rows with invoice number: SEGOTFSI/1027782.
    And J281 contains all the markets from column I on the rows with invoice number: F142420

    That's how I want the formula to work. Exept that instead of showing every market in J, I want it to just show the unique markets.
    So when invoice: SEGOTFSI/1027782 contains these markets:
    PL, PL, PL, PL, PL, PL, PL, PL, PL, PL, IT, GB, GB, GB, GB, GB, GB, SE, GB, IT, PL, SE
    I want it to be showed in J3 as:
    PL, IT, GB, SE
    Last edited by Victorjo; 07-04-2018 at 09:28 AM.

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    Hi,

    First of all, you absolutely should not use entire column references within an array formula. I appreciate that your range is dynamic and subject to change, and that, by referencing entire columns, you are 'covering your bases'. However, array formulas calculate over all cells within the ranges passed to them, whether beyond the last-used cells in those ranges or not. As such, an array formula which references an entire column is being forced to calculate more than a million more cells than one which references, say, 6000 rows. And that's for just one instance of that formula.

    Either use dynamic ranges or reduce the end row being referenced to a suitably low, though sufficient, value.

    In J3:

    =IF(C3<>C2,TEXTJOIN(", ",1,IF(FREQUENCY(IF(ISNUMBER(SEARCH(E3,E$3:E$6000)),MATCH(I$3:I$6000,I$3:I$6000,0)),ROW(I$3:I$6000)-MIN(ROW(I$3:I$6000))+1),I$3:I$6000,"")),"")

    Regards
    Click * below if this answer helped

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

  11. #11
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    Hey Victor,

    I'd create a Helper Column with this formula and only use your states if the helper is =1.

    =COUNTIFS(I$2:I3,I3,E$2:E3,E3)

    See attached with the helper column.

    Need Helper Column.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  12. #12
    Registered User
    Join Date
    07-04-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    8

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    XOR LX, your formula worked but it was very resourse intensive, excel bacame practically unusable to me due to the sluggishness when using it .

    I tried MarvinP's Idea and this is the solution I came up with

    Capture now.PNG

    Helper #1 column contains this formula:
    Please Login or Register  to view this content.
    Helper #2 column formula:
    Please Login or Register  to view this content.
    Markets formula:
    Please Login or Register  to view this content.
    I had to include Helper #2 because otherwise the Markets formula treat 1, 10-19, 21, 31, etc. as conditions to search for instead of just 1.

    One weird thing I noticed is the when I use filters the Markets formula doesn't show the result even thought its still in the cell, so you have to reapply it.
    Attached Files Attached Files
    Last edited by Victorjo; 07-06-2018 at 01:25 PM.

  13. #13
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    Quote Originally Posted by Victorjo View Post
    XOR LX, your formula worked but it was very resourse intensive, excel bacame practically unusable to me due to the sluggishness when using it .
    I take it you heeded my advice about not using entire column references?

    With the formula I posted (upper row reference reduced to 6000) on the workbook you supplied, a full calculation took barely no time at all.

    Regards

  14. #14
    Registered User
    Join Date
    07-04-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    Office 365
    Posts
    8

    Re: How can I modify my array TEXTJOIN formula to output only unique values

    XOR LX, I did use your formula with reference 3 to 6000, but it was still very resourse intensive. I even whent and changed all my other formulas in my document which referenced the entire column to only reference 3 to 6000. All this time I thought that referencing the entire column meant it only looked at all active rows, so thank you for that advice
    Last edited by Victorjo; 07-06-2018 at 01:53 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 11
    Last Post: 10-27-2016, 07:33 PM
  2. Find Unique Values - Need Not Array Formula
    By Neyme in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2016, 04:36 PM
  3. [SOLVED] Array to embed formula and if number of output is met output Blank
    By ywang in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-29-2015, 02:34 AM
  4. add to array formula to only return unique values
    By jason892 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2013, 06:39 AM
  5. [SOLVED] Count unique values in list but NOT using ARRAY formula
    By alx0101 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-15-2013, 08:15 AM
  6. Modify Unique List Array
    By hassankhan in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-11-2009, 12:28 AM
  7. Replies: 0
    Last Post: 05-19-2008, 11:43 PM

Tags for this Thread

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