Merging several dates into one cell and separate them by commas

[netoine]

Hello,

I have extracted the dates our courses are given from a file. I have created a pivot table but it's not quite the format I want.
I would like to group every date by month in the same cell, separated by commas (see the "Proper format" of my file).
That's one venue per line and all the dates of each month separated by a comma

That gives something like

venue/month	8	9	10	11	12
S01	15	4,21,28	9,24	9,20,29	11
S02	23	13,22	2,13,23	15	6

It seems rather straight forward but I can't get it done... and it would be a nightmare to do it manually.
Would someone here have a solution for me please (I suppose a formula would do the trick)?

Thanks a lot!

[netoine]

Thanks Ali, I've changed it. Sorry about that (forgot to edit before I posted)

[Syrkrasi]

Is this what you're looking to do... see attachment

[netoine]

Thanks for your help but that's not it.

I'd like to have one venue per line and all the dates of each month separated by a comma, just like in my "Proper format" tab

That gives something like

venue/month	8	9	10	11	12
S01	15	4,21,28	9,24	9,20,29	11
S02	23	13,22	2,13,23	15	6

Thanks

[Syrkrasi]

Your raw data have formatting issues whereas I had to insert the data into notepad then re-insert it back into Excel.

See attachment

[netoine]

Ok I suppose I'm not clear enough, sorry about that.
What I would like is the day(s) a course is given in the venue in a month. If I take August with S01 and S02 it means that there is going to be a course on the 15th in S01 and one other on the 23rd in S02. For September, there will be 3 courses in S01 (on the 4th, 21st and 28th) and 2 in the S02 (on the 13th and 22nd)

Hope that helps.
Thanks!

[JeteMc]

This proposed solution employs helper columns, which may be hidden for aesthetic purposes, on the 'Raw Data' sheet.
The formula that populates the helper columns is:

Please Login or Register  to view this content.

The formula that populates the column of venues on the 'Proper Format' sheet is:

Please Login or Register  to view this content.

The array entered formula that populates columns B:F on the 'Proper Format' sheet is:

Please Login or Register  to view this content.

The venues column on the 'Proper Format' sheet has conditional formatting applied to hide the #NA errors that occur after the end of the venues list has been reached.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[netoine]

You're the best, that's exactly it, thank you so much!!!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.