MCONCAT leaves spaces for unmatched values

[darkgear.com]

This is my first attempt at making a departmental calendar to track vacation and also to view who is taking vacation on what day. I also want to be able to filter by individual departments.

I started with a simple entry form.

2006-Calendar.xls
	A	B	C	D
1	Date	EE	Dept	PTO
2	1/1/06	Albert	USS	8
3	1/1/06	Mercy	Rating	8
4	1/3/06	Naomi	Records	8
5	1/4/06	Dustin	Records	8
6	1/5/06	Albert	USS	4
7	1/5/06	Amy	Rating	8
8	1/5/06	Donna	Clerk	8
9	1/5/06	Dorothy	Clerk	8
10	1/5/06	Dustin	Records	8
11	1/5/06	Linda	Records	8
12	1/5/06	Miriam	Rating	2
13	1/5/06	Naomi	Records	4
14	1/5/06	Polly	Rating	8
15	1/5/06	Sophia	Records	8
16	1/6/06	Donna	Clerk	8
17	1/6/06	Nita	Rating	8
Summaries

I then made a calendar and started working on the formula to display who was on vacation for the day and made it possible to filter by department.

2006-Calendar.xls
	I	J	K	L
1		Department
2	Thursday		Friday
3		5		6
4	Albert Amy Donna Dorothy Dustin Linda Miriam Naomi Polly Sophia		Donna Nita
Jan

K1 is data validated so you can select one of the departments.

Ugly formula I used to get the names to display...

{=IF($K$1="",MCONCAT(IF(VALUE(CONCATENATE("1","/",J3,"/","2006"))='Data Entry'!$A$2:$A$70,'Data Entry'!$B$2:$B$70,"")," "),MCONCAT(IF(VALUE(CONCATENATE("1","/",J3,"/","2006"))='Data Entry'!$A$2:$A$70,IF($K$1='Data Entry'!$C$2:$C$70,'Data Entry'!$B$2:$B$70,"")," ")))}

My question is is there a better way to handle the display of names? Using MCONCAT works but it places an extra space(or comma or whatever I use to separate names) after every row that it did NOT find a match. So towards the end of the year I could end up with scores of spaces before the first name shows up.

Best regards,
Randy

 

[Brian from Maui]

Randy,

Since you're from Hawaii, I have a workable calendar. PM your email address if you're interested.

 

[Aladin Akyurek]

=IF($K$1<>"",SUBSTITUTE(MCONCAT(IF('Data Entry'!$A$2:$A$70=(J3&"-Jan-2006")+0,IF('Data Entry'!$C$2:$C$70=$K$1," "&'Data Entry'!$B$2:$B$70,""),""))," ","",1),"")

which must be confirmed with control+shift+enter.

 

[darkgear.com]

Aladin,

Thanks! That works.

Is there a way to do this without using MCONCAT?

 

[Aladin Akyurek]

Aladin,

Thanks! That works.

Is there a way to do this without using MCONCAT?

Look for ACONCAT of which the code in VBA is available here in some threads.