+ Reply to Thread
Results 1 to 6 of 6

Removing duplicates from a TEXTJOIN

  1. 04-01-2016, 12:10 PM #1
    CKPHarry
    CKPHarry is offline
    Registered User
    Join Date
    04-01-2016
    Location
    Boca Raton, FL
    MS-Off Ver
    365
    Posts
    2

    Removing duplicates from a TEXTJOIN

    I have a large table of items for sales in many states. The two letter state abbreviation for that particular client is in a column. The clients are listed in order of the date of the transaction, so many states are duplicated quite a few times. My CEO wants me to create a cell that lists the states where we currently have active clients. I have done this via the following formula:

    =TEXTJOIN(",",TRUE,B10:B500)

    This works, but includes duplicates of states. For example, it's returning the result: AZ,CA,FL,TX,FL,CA,CA,KY, etc. Is there any way to have it list the states, without the duplicates?
    Or is there another way entirely to accomplish this? Help!
    Reply With QuoteRegister To Reply
  2. 04-01-2016, 03:54 PM #2
    tony h
    tony h is offline
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Removing duplicates from a TEXTJOIN

    Not the most obvious answer but this should work.

    You need to create a VBA module Alt+F11 to open VBA and Insert ... module
    Then paste the code below into the module

    After that (you may need to save the file first and you will have to save as a type .xlsm) on opening ensure Macros are enabled.

    Now on the worksheet use =getUniquesSorted(",",B10:B500)

    Please Login or Register  to view this content.

    Note depending on the volume of data this may prove slow.


    click on the * Add Reputation if this was useful or entertaining.
    Reply With QuoteRegister To Reply
  3. 04-01-2016, 04:23 PM #3
    CKPHarry
    CKPHarry is offline
    Registered User
    Join Date
    04-01-2016
    Location
    Boca Raton, FL
    MS-Off Ver
    365
    Posts
    2

    Re: Removing duplicates from a TEXTJOIN

    Almost there, thanks! I entered 10 samples into the worksheet (state abbreviations chosen from a drop down menu), and it returns only a few of them, though it does successfully eliminate duplicates. Any idea as to why? This thing is driving me crazy today.

    Never mind! I pasted it again and it's working! This is great, Tony. Many thanks!!
    Last edited by CKPHarry; 04-01-2016 at 04:39 PM.
    Reply With QuoteRegister To Reply
  4. 04-01-2016, 04:47 PM #4
    tony h
    tony h is offline
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Removing duplicates from a TEXTJOIN

    Jolly good.
    Reply With QuoteRegister To Reply
  5. 01-02-2020, 05:56 PM #5
    Most_Triumphant
    Most_Triumphant is offline
    Registered User
    Join Date
    01-02-2020
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    2

    Re: Removing duplicates from a TEXTJOIN

    Long shot here, but Tony h, I can't seem to get your code to work. I am receiving a compile error that reads "Expected End Function." Any idea what's going wrong?
    Reply With QuoteRegister To Reply
  6. 01-02-2020, 05:57 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Removing duplicates from a TEXTJOIN

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, or you want help with a related matter, 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
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Remove duplicates function not removing duplicates
    By Berilium2 in forum Excel General
    Replies: 3
    Last Post: 04-01-2015, 06:55 AM
  2. Removing duplicates
    By Krys14 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2014, 08:13 AM
  3. Excel 2007 : How to keep ONE instance of duplicates while removing duplicates AND unique values
    By cjh_2012 in forum Excel General
    Replies: 6
    Last Post: 03-29-2012, 12:16 AM
  4. Removing duplicates
    By nuno in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2007, 08:29 AM
  5. [SOLVED] Removing Duplicates
    By sat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2005, 07:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1