Excel challenge

[Gene Khalyapin]

I know there must be a solution for this, just can't figure it out:

Row 1 has a set of triggeres (in different columns): 1,0,1,0,1
Row 2 has corresponding values: a,b,c,d,e

I need a formula that would look at triggers and for each trigger equal to 1, it should pick up the values from Row 2 and combines them in one sell. In the example above the desired result should be: "ace"

Is there an expandable formula solution for this?

Thanks!

 

[Gene Khalyapin]

Thank you guys! "string concatenation" is exactly what I need but it looks like there is no easy solution for this and the ones that are available involve Add-in, VBA code or lots of extra data (I had hoped for some easy and neat solution like that Barry's =LOOKUP() function in 2008 Excel challenge)

Thank you for the help!

 

[yytsunamiyy]

Triggered by DonleyOte's answer in this thread: http://www.mrexcel.com/forum/showthread.php?t=494235

is here an improved version of the formula "wizard":

Sub makeFormula()
Dim StrFormula As String
Dim rng As Range
Dim cll As Range
On Error Resume Next
 
[B][COLOR=red]Set rng = Application.InputBox("Select the range the formula should apply to:", Type:=8)
[/COLOR][/B]If Not rng Is Nothing Then
    If rng.Columns.Count <= 255 Then
        StrFormula = "=Concatenate("
        For Each cll In rng
            StrFormula = StrFormula & "IF(" & cll.Address & "=1," & cll.Offset(1, 0).Address & "," & """" & """" & "),"
        Next cll
        StrFormula = Left(StrFormula, Len(StrFormula) - 1)
        StrFormula = StrFormula & ")"
        ActiveCell.Formula = StrFormula
    Else
        MsgBox "You have choosen more than 255 columns. No Formula generated."
    End If
Else
    MsgBox "You have not selected a valid range. No Formula generated."
End If
End Sub