+ Reply to Thread
Results 1 to 9 of 9

Concatinate string of text and remove duplicates

  1. #1
    Registered User
    Join Date
    03-19-2015
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    4

    Concatinate string of text and remove duplicates

    Hello,

    I hope someone can help with this!

    I have a data set with 3 columns. The first column contains a list of projects (all unique). The second column contains a list of companies (several duplicates) and the third column contains a list of states (several duplicates). For each company, I need to provide a list of the states they are operating out of. I'm using AConcat to do this, which works well, except I don't want to have duplicates in the list.

    Can someone help with a function or modification to the VBA to produce a concatenated list in a cell that does not contain any duplicates?

    Thank you!!

    M

  2. #2
    Registered User
    Join Date
    03-18-2015
    Location
    Wisconsin, USA
    MS-Off Ver
    2010
    Posts
    14

    Re: Concatinate string of text and remove duplicates

    Why don't use just do a remove duplicates? Select all three columns of data, go to the "Data" tab, select "Remove Duplicates". Make sure only the columns for Company Name and State are checked. Once you have a clean list to work with, you can easily do a concatenate formula or a pivot table.

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Concatinate string of text and remove duplicates

    Submit an example workbook with a good representation of the data you are working with.

    Also, clearly demonstrate where you want this "list" to go

  4. #4
    Registered User
    Join Date
    03-19-2015
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Concatinate string of text and remove duplicates

    Vendor List temp2.xlsm

    Iíve attached the Excel File. The Project List tab contains the data set in a table. The Property column contains the name of each project, which is unique. Each project is located in a state and there can be numerous projects in each state, so there are duplicate states listed in the State column. Each project has an architect and there are fewer architects than there are projects, so again, there are duplicate architects. I donít want to just eliminate all of the duplicates for several reasons, i.e. this is a live document and I donít want to have people go through the exercise of eliminating duplicates every time they need to view the data and this is a record of work. I donít want to eliminate duplicates because I donít want to alter the data.

    The Vendors tab contains the output. We always want to know which states our different architects have worked in. The State column uses the AConcat function to list each state the architect has worked in. The issue is that since the architects repeatedly work in the same states, I will end up with someone like Arch 3 who has OH listed 3 times.

    Thanks for the help!

    M

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Concatinate string of text and remove duplicates

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-19-2015
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Concatinate string of text and remove duplicates

    Thank you stnkynts. That works in spirit, but it replaces the formula in the cell with the concatenated text minus the duplicates. The issue then is that as we add more projects with more states, the concatenated string of text won't update.

    Any other ideas?

    M

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,915

    Re: Concatinate string of text and remove duplicates

    Change the function to
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-19-2015
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Concatinate string of text and remove duplicates

    That did it!! Thank you Jindon!!

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,915

    Re: Concatinate string of text and remove duplicates

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. remove duplicates from csv string
    By nikko50 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2014, 01:24 PM
  2. Concatinate and remove duplicates using vba macro using autofilter criteria
    By chakravarthysm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2013, 03:30 PM
  3. [SOLVED] concatinate cells and remove duplicates
    By wellsw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2013, 07:54 PM
  4. Concatinate the text of each row
    By dumiduw in forum Excel General
    Replies: 4
    Last Post: 05-29-2012, 07:27 AM
  5. embedding +concatinate string in lookup
    By Hans_81 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2006, 02:07 AM

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