days of week list challenge

[mwc0914]

Here is a bit a of a challenge...
Say I have some days of week marked in cells by an "X", and a cell where I am currently entering the days of service.
I am looking for a formula in days of service cell that examines the cells to its left for an "X", and derives a concatenated list of the correct days

Thanks

[TABLE="width: 584"]
<tbody>[TR]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Days of Service [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]Tuesday, Thursday[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]

 

[mrshl9898]

Assuming, the headings are in A1:F1.

In F2

=IF(RIGHT(IF(A2="x","Monday, ","")&IF(B2="x","Tuesday, ","")&IF(C2="x","Wednesday, ","")&IF(D2="x","Thursday, ","")&IF(E2="x","Friday",""),2)=", ",LEFT(IF(A2="x","Monday, ","")&IF(B2="x","Tuesday, ","")&IF(C2="x","Wednesday, ","")&IF(D2="x","Thursday, ","")&IF(E2="x","Friday",""),LEN(IF(A2="x","Monday, ","")&IF(B2="x","Tuesday, ","")&IF(C2="x","Wednesday, ","")&IF(D2="x","Thursday, ","")&IF(E2="x","Friday",""))-2),IF(A2="x","Monday, ","")&IF(B2="x","Tuesday, ","")&IF(C2="x","Wednesday, ","")&IF(D2="x","Thursday, ","")&IF(E2="x","Friday",""))

 

[lrobbo314]

Here's a custom function if you'd like.

Function dConcat(iRng As Range) As StringDim AR()
Dim wDays()
Dim Result As String


wDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
AR = iRng.Value


For i = LBound(AR(), 2) To UBound(AR(), 2)
    If AR(1, i) = "x" Or AR(1, i) = "X" Then
        Result = Result & wDays(i - 1) & ", "
    End If
Next i


If Len(Result) > 0 Then
    Result = Left(Result, Len(Result) - 2)
    dConcat = Result
Else
    dConcat = ""
End If


End Function

The formula would look like

=dConcat(A2:E2)

 

[Peter_SSs]

If you have Excel 2016 via Office 365, would this be any use?
It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Book1
	A	B	C	D	E	F
1	Mon	Tue	Wed	Thu	Fri	Days of Service
2		x		x		Tue, Thu
Concat Days

Cell Formulas
Range	Formula
F2	{=TEXTJOIN(", ",TRUE,IF(A2:E2="x",A$1:E$1,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

If you have Excel 2016 via Office 365, ...

Otherwise possibly this?

Book1
	A	B	C	D	E	F
1	Mon	Tue	Wed	Thu	Fri	Days of Service
2		x		x		Tue, Thu
Concat Days

Cell Formulas
Range	Formula
F2	=SUBSTITUTE(TRIM(IF(A2="","",A$1)&IF(B2="",""," "&B$1)&IF(C2="",""," "&C$1)&IF(D2="",""," "&D$1)&IF(E2="",""," "&E$1))," ",", ")