Avoiding commas in formula if no value exists

Challis

New Member
Joined
Oct 22, 2017
Messages
21
Hi guys,
I have this formula which matches two criteria and returns upto 4 values all in the same cell.
It works well and as i intended but one thing I'd like to address is only putting commas in if a trailing value is returned.
So my question is, if no value is returned, is there a way to not show the trailing comma.
So say for example if one value was returned "A" it would just show "A". If two values were returned "A" & "B"" it would show "A,B".

Code:
=IFERROR(INDEX(Schedule!C:C,SMALL(IF((Schedule!$P$6:$P$1000=Dashboard!$A3)*(Schedule!$S$6:$S$1000={"Inspecting","Authoring Report"}),ROW(Schedule!$C$6:$C$1000)),ROWS(C$2:C$2))),"")&", "&IFERROR(INDEX(Schedule!C:C,SMALL(IF((Schedule!$P$6:$P$1000=Dashboard!$A3)*(Schedule!$S$6:$S$1000={"Inspecting","Authoring Report"}),ROW(Schedule!$C$6:$C$1000)),ROWS(C$2:C$3))),"")&", "&IFERROR(INDEX(Schedule!C:C,SMALL(IF((Schedule!$P$6:$P$1000=Dashboard!$A3)*(Schedule!$S$6:$S$1000={"Inspecting","Authoring Report"}),ROW(Schedule!$C$6:$C$1000)),ROWS(C$2:C$4))),"")&", "&IFERROR(INDEX(Schedule!C:C,SMALL(IF((Schedule!$P$6:$P$1000=Dashboard!$A3)*(Schedule!$S$6:$S$1000={"Inspecting","Authoring Report"}),ROW(Schedule!$C$6:$C$1000)),ROWS(C$2:C$5))),"")
Thanks
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Can you show various sample results from the formula that contain the unwanted , commas?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe...(untested)
Use a space as separator rather than ", " and try
=SUBSTITUTE(TRIM(formula here)," ",", ")

Hope this helps

M.
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
The above formula doesn't work properly with the values returned by the INDEX functions contain spaces.
Maybe something like this
=SUBSTITUTE(IFERROR(", "&INDEX(.....),"")&IFERROR(", "&INDEX(.....),"")&IFERROR(", "&INDEX(.....),"")&IFERROR(", "&INDEX(.....),""),", ","",1)

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,508
Messages
5,469,034
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top