2

I'm looking at a large database of 1s and 0s with named columns, like this:

red    blue   green  orange purple
────── ────── ────── ────── ──────
0      0      1      0      1 
0      1      0      0      0

I want to concatenate all the headings (by row) where the row has a "1" below that heading. So ideally the first one would equal "green, purple" and the second would just read "blue". I have a large amount of data so anything with nesting of a hundred "IF" functions doesn't make sense.

I've tried

=IF(B1:B5=1, CONCATENATE(A1:A5), "")

and a couple things close to that, but I'm not finding an obvious way to get it. I also don't really have time or enough knowledge to deal with VBA. I appreciate all help, thanks!

GSara
  • 21
  • 4

2 Answers2

2

String concatenation over more than a few cells is best left to a VBA User Defined Function (aka UDF) even without setting criteria. Your situation of "nesting of a hundred "IF" functions" would certainly put it in this category.

Tap Alt+F11 and when the VBE opens, immedaitely use the pull-down menus to Insert ► Module (Alt+I,M). Paste the following into the new pane titled something like Book1 - Module1 (Code).

Public Function conditional_concat(rSTRs As Range, rCRITs As Range, Optional sDELIM As String = ", ")
    Dim c As Long, sTMP As String
    For c = 1 To Application.Min(rSTRs.Cells.Count, rCRITs.Cells.Count)
        If CBool(rCRITs(c).Value2) Then _
            sTMP = sTMP & rSTRs(c).Value & sDELIM
    Next c
    conditional_concat = Left(sTMP, Application.Max(Len(sTMP) - Len(sDELIM), 0))
End Function

Tap Alt+Q to return to your worksheet. Use this UDF like any native Excel worksheet function. The syntax is,

conditional_concat(<range of strings>, <range of conditions>, [optional] <delimiter as string>)

      Conditional String Concatenation

The formula in G2 is,

=conditional_concat(A$1:E$1, A2:E2)

Fill down as necessary.

0

The way I would do it would be to add five extra columns and explicitly enter 'red', 'blue', 'green', 'orange', 'purple' where the corresponding column is a 1 so you could do:

Red column 'r': =IF(col_red=1,"red,","")

Blue column 'b': =IF(col_blue=1,"blue,","")

Green column 'g': =IF(col_green=1,"green,","")

orange column 'o': =IF(col_orange=1,"orange,","")

Purple column 'p': =IF(col_purple=1,"purple,","")

and in another column concatenate these columns (change the references to the respective columns etc) =LEFT(F2 & G2 & H2 & I2 & J2,LEN(F2 & G2 & H2 & I2 & J2)-1) (I found the concatenate function errored because of the erroneous rows

Gives this result: Result of the functions listed above in answer

I have experimented a little with array functions with no success which may be a way to solve this.

joelc
  • 331
  • 3
  • 9