Concatenate top row cells if column below has 1

by

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.

More Related Contents:

  1. TEXTJOIN for xl2010/xl2013 with criteria
  2. If two cells match, return value from third
  3. MS Excel – Concat with a delimiter
  4. Concatenate multiple ranges using vba
  5. how should I apply a formula and get answer in all the odd cells?
  6. Sequence of numbers based on start and end value of 2 cells [closed]
  7. Function to convert column number to letter?
  8. Vlookup using 2 columns to reference another
  9. Better way to find last used row
  10. 2 Dimensional array from range [closed]
  11. How can I create a calendar input in VBA Excel?
  12. Using Google Chrome in Selenium VBA (Installation Steps)
  13. Excel formula to get cell color [duplicate]
  14. Dir() function not working in Mac Excel 2011 VBA
  15. How to use workbook.saveas with automatic Overwrite
  16. Range limit conundrum
  17. Using LocationTextExtractionStrategy in itextSharp for text coordinate
  18. How do you run a .exe with parameters using vba’s shell()?
  19. Iterating through populated rows
  20. Using VBA to detect which decimal sign the computer is using
  21. Loop through each row of a range in Excel
  22. Fill formula down till last row in column
  23. Outputting Excel rows to a series of text files
  24. How to create a range from two ranges in VBA?
  25. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  26. If string contains word from list, return value adjacent to list value
  27. excel vba freeze pane without select
  28. Converting a string representation of a constant into a constant?
  29. Write from R into template in excel while preserving formatting
  30. How to protect Excel workbook using VBA?

Leave a Comment