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

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.

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

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

Please Login or Register  to view this content.

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

Change the function to

Please Login or Register  to view this content.

That did it!! Thank you Jindon!!

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