asked on

Construct a string based on checked cells

Hoping there's a way in Excel to do this - see picture - I want each column to check for an "x" and if found, append a code at right to a cell at top. Hopefully the picture is worth a 1000 words (or more). Not sure what formula, if any can do this... Can vaguely see a VBA way but would rather avoid that.

ASKER CERTIFIED SOLUTION

Gerwin Jansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lee W, MVP

ASKER

Great, thanks, I'll check it out.

it_saige

It's possible but it wouldn't be very pretty without some VBA code:

To explain, the 'G' column becomes your temporary holder for 'C' column values, 'H' for 'D'; etc. etc.
Once you have these values then you can concatenate the holder values -

Personally, I would use a Function to do all this work for me. If you want I can whip one up.

-saige-

it_saige

Nevermind on whipping one up, Gerwin's posted PAQ does this nicely. ;)

-saige-

it_saige

Gerwin's posted EE PAQ in action:

Repost of code used from http:/Q_26884303.html -

' Credit to Rory Archibald;
' EE PAQ - http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26884303.html
Function CONCATENATEIF(Source As Range, Criteria, Target As Range, Optional Delimeter As String = ", ")
    Dim fSource, fTarget
    Dim fRow As Long, fColumn As Long
    If (Source.Rows.Count <> Target.Rows.Count) Or (Source.Columns.Count <> Target.Columns.Count) Then
        CONCATENATEIF = CVErr(xlErrRef)
    Else
        fSource = Source.Value
        fTarget = Target.Value
        For fRow = LBound(fSource, 1) To UBound(fSource, 1)
            For fColumn = LBound(fSource, 2) To UBound(fSource, 2)
                If StrComp(fSource(fRow, fColumn), Criteria, vbTextCompare) = 0 Then
                    CONCATENATEIF = CONCATENATEIF & Delimeter & fTarget(fRow, fColumn)
                End If
            Next fColumn
        Next fRow
        If Len(CONCATENATEIF) > 0 Then
            CONCATENATEIF = Mid$(CONCATENATEIF, Len(Delimeter) + 1)
        Else
            CONCATENATEIF = ""
        End If
    End If
End Function

Open in new window

-saige-

Lee W, MVP

ASKER

To be clear for others, you have to open the VBA editor, add a new module, and paste the function code from the PAQ in there. Then it works great.

THANKS!

Gerwin Jansen

You're welcome ;)