Concatenate list of dates into one cell without duplicates

I wish to concatenate a list of dates in a range (C3:C9) into another cell (say L40) without duplicates so for example my list of dates is:

06/03/2013
06/03/2013
06/03/2013
08/03/2013
08/03/2013
08/03/2013
09/03/2013

The output in L40 would be:

06/03/2013, 08/03/2013, 09/03/2013


I currently have a working solution in that I use a public function (shown below):

Please Login or Register  to view this content.

And then call the function in L40 like so:

Please Login or Register  to view this content.

But for some reason one of my colleagues computers does not like this and highlights the word Trim in the VBA code.

So I'm either looking for an alternative or anyone to shed light on why his Excel would not like the word Trim.

Thanks if anyone can help.

That function has a bug...
If the values like
12
2
12
30
3
20
10
It will miss 2 and 3 due to nature of Instr function....

try

=ConcatUniq(C3:C9,", ")

Please Login or Register  to view this content.

Hi Jindon,

That seems to work but it displays the dates as numbers e.g. 41340, 41341. I have tried formatting the cell but it does not change.

Is there anyway of showing them in date format using this method?

Thanks for your help.

Hummmm, then

Please Login or Register  to view this content.

Hi Jindon,

That works great, but is there anyway of getting rid of the comma after the last date?

Marcol, I have Googled this and it seems to be a common problem with trim, left, right, etc. functions. I found that if you go into References there will often be one that states MISSING that is now ticked after the problem occurs. If you untick it and try again, it works, but once you've closed and re-opened the workbook, the problem occurs again.

If you have blank(s) within the range then
Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Great, thanks fingers crossed this works ok on my colleagues computer.

Hi Marcol,

I just gave it a quick Google and saw at least 5 pages of results of people posting the same type of question to forums etc.

My colleague uses Excel 2007 and we installed the latest SP last week when we were trying to solve the problem.

I will keep the lines of code you have provided to hand just in case, I did try vba.Trim because someone on one of the various forums recommended it but my colleague didn't have a chance to try that before I found the different solutions from Jindon & yourself.

Thanks