MCONCAT leaves spaces for unmatched values

darkgear.com

New Member
Joined
Mar 10, 2005
Messages
10
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
ABCD
1DateEEDeptPTO
21/1/06AlbertUSS8
31/1/06MercyRating8
41/3/06NaomiRecords8
51/4/06DustinRecords8
61/5/06AlbertUSS4
71/5/06AmyRating8
81/5/06DonnaClerk8
91/5/06DorothyClerk8
101/5/06DustinRecords8
111/5/06LindaRecords8
121/5/06MiriamRating2
131/5/06NaomiRecords4
141/5/06PollyRating8
151/5/06SophiaRecords8
161/6/06DonnaClerk8
171/6/06NitaRating8
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
IJKL
1Department
2ThursdayFriday
356
4Albert Amy Donna Dorothy Dustin Linda Miriam Naomi Polly SophiaDonna 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
:biggrin:
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Randy,

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
=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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
darkgear.com said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,645
Members
412,335
Latest member
cinciri99
Top