I'm going to use a combination of formulas and VBA. See the attachment.
First, the formula to remove duplicates. To the right of your current data, (in my example, I started in Column K)
=IF(COUNTA($A1:$J1)>=COLUMNS($A$1:A$1),IF(COUNTIF($A1:A1,A1)>1,"",A1),"")
copy this to the right as far as you need and down as far as you need
Then I used TigerAvatar's UDF (user defined function) ConcatAll(range, delimiter)
I'll explain how to put the code in, next.
So to the right of your new cells, (Column V in my case)
=ConcatAll(K1:U1, ", ") I used "comma space" as my separater/delimiter)
copied down.
The code below must be copied and pasted into a module.
ALT + F11 to open the Visual Basic Editor
Insert (from the menu) > Module
Then paste it in and close the Visual basic editor
Lastly, to remove the formula, and make it static. Copy column V and Paste Special> Values
Questions?
Bookmarks