Conditional Concatenation in Excel

Question

As you can see in the image, there are some 1 and 0s rearranged in 3 rows and one English Alphabet for each column. What I need to do is concatenate the English Alphabets for each row when the respective column value is 0. How can I do it?

Answer 1

The following array formula will do the job (enter it with Ctrl+Shift+Enter):

=CONCAT(IF($A1:$I1=0,UNICHAR(64+COLUMN($A1:$I1)),""))

For older Excel versions, use the legacy functions CONCATENATE() and CHAR() in place of these functions.

Answer 2

Conditionally Concatenate Row (UDF)

Arguments

• SourceRowRange: The range containing the values that will be written toCCROW e.g. A, B, C ... Required.
• CriteriaRowRange: The range that will be checked for CriteriaValue. Required.
• CriteriaValue: The value that the cells in CriteriaRowRange will be checked against. Default is 0. Optional.
• JoinString: The value that will be put between the values that will be written to CCROW. Default is "". Optional.

' Copy the following code to a standard module i.e. in VBE go to Insert>Module.

The Code

Function CCROW(ByVal SourceRowRange As Range, ByVal CriteriaRowRange As Range, _
        Optional ByVal CriteriaValue As Variant = 0, _
        Optional ByVal JoinString As String) As String

    Dim vntS As Variant   ' Source Array
    Dim vntC As Variant   ' Criteria Array
    Dim NoC As Long       ' Number of Columns
    Dim j As Long         ' Arrays Column Counter
    Dim strB As String    ' String Builder
    Dim strC As String    ' Criteria String

    ' Calculate number of columns of the narrower Range.
    NoC = WorksheetFunction.Min(SourceRowRange.Columns.count, _
            CriteriaRowRange.Columns.count)

    ' Copy resized (adjust them to same size) Ranges to Arrays.
    vntS = SourceRowRange.Resize(1, NoC)
    vntC = CriteriaRowRange.Resize(1, NoC)

    ' Loop through columns of either Array.
    For j = 1 To NoC
        ' Write current value of Criteria Array to Criteria String.
        strC = vntC(1, j)
        ' Check if Criteria String is NOT empty.
        If strC <> "" Then
            ' Check if Criteria String is equal to Criteria Value.
            If strC = CriteriaValue Then
                ' Check if String Builder is NOT empty.
                If strB <> "" Then  ' NOT empty.
                    strB = strB & JoinString & vntS(1, j)
                  Else              ' IS empty (only once).
                    strB = vntS(1, j)
                End If
            End If
        End If
    Next

    ' Write String Builder to Conditionally Concatenate Row.
    CCROW = strB

End Function

Usage in Excel

=CCROW(A$1:I$1,A3:I3) ' Result: ADG
=CCROW(A$1:I$1,A4:I4) ' Result: CFI
=CCROW(A$1:I$1,A5:I5) ' Result: DG

If you add JoinString:

=CCROW(A$1:I$1,A3:I3,,",")   ' Result: A,D,G
=CCROW(A$1:I$1,A3:I3,0,",")  ' Result: A,D,G
=CCROW(A$1:I$1,A3:I3,0,", ") ' Result: A, D, G

IF you change CriteriaValue:

=CCROW(A$1:I$1,A3:I3,1) ' Result: BCEFHI
=CCROW(A$1:I$1,A4:I4,1) ' Result: ABDEGH
=CCROW(A$1:I$1,A5:I5,1) ' Result: ABCEFHI

Remarks Lock ($) the row of SourceRowRange to keep it the same when the formula is copied down.

Answer 3

Here, add this function to a module. You can then call it directly via excel. Nice one.

Function conc(ref As Range, Optional Separator As String) As String
Dim Cell As Range
Dim Result As String

For Each Cell In ref
    If Cell.Value = 0 Then
    Result = Result & chr(64 + Cell.Column) & Separator
    End If
Next Cell
If Separator <> "" Then conc = Left(Result, Len(Result) - 1) Else: conc = Result
End Function

Answer 4

Here is a VBA solution that can handle any number of columns (assuming that the letter associated with each column is the standard column label):

Function ZeroColumns(R As Range) As String
    Dim n As Long
    Dim count As Long
    Dim cols As Variant
    Dim cell As Range

    n = R.Cells.count
    ReDim cols(1 To n)
    For Each cell In R.Cells
        If cell.Value = 0 Then
            count = count + 1
            cols(count) = Split(cell.Address, "$")(1)
        End If
    Next cell
    ReDim Preserve cols(1 To count)
    ZeroColumns = Join(cols, "")
End Function

The code shouldn't be too hard to tweak if the stated assumption doesn't hold.

Answer 5

Are you going to this to many more columns, or just the ones you've mentioned? As long as the number of columns is relatively small, as in your picture, you can concatenate IF functions to achieve your result.

Here's what I did:

Using that formula will get you a result like the one you have:

Assuming also that you have the values in a worksheet like mine, just paste the formula =IF(B3=1,"",B$1)&IF(C3=1,"",C$1)&IF(D3=1,"",D$1)&IF(E3=1,"",E$1)&IF(F3=1,"",F$1)&IF(G3=1,"",G$1)&IF(H3=1,"",H$1)&IF(I3=1,"",I$1)&IF(J3=1,"",J$1) in B7 and then drag to B8 and B9 to get the rest of the results.

Of course, if you are going to do this for many more columns, it's maybe best to use VBA.

Answer 6

You can do it all in one formula if you like:

=CONCATENATE(IF($A1=0,'A',''),IF($B1=0,'B',''), ...)

Or put the intermediate strings in a separate row and then concatenate them (to save wear and tear on your fingers).