asked on

Excel VBA concatenate table header txt for row/col intersections marked with "X"

I have a dynamic table with "i" rows and "j" columns.
The top row will have header labels.
The left-most column cells will be enumerated 1, 2, 3...
The next column will be populated by VBA
Besides these columns 1 & 2, the only content in the body of the table are "X" or blank.

I need help with the code that will enter into each cell of col 2 the concatenated text of the header label corresponding to each cell with an "X".

Table will be appox 150rows x 30col so looping will probably be slow and should be avoided if possible.

Thanks, --Andres

Rory Archibald

Do you want the headers all joined up, or separated with a delimiter such as a space or comma?

Rory Archibald

One other question: this could be done using a subroutine to populate the column with hardcoded values, or you could use a VBA UDF located in each cell, so the formulas would be live. Any preference?

AndresHernando

ASKER

rorya, thanks for your attention. In answer to your q's:
1. Separated by coma and a space
2. No preference on whether live or hard-coded. Whichever is "lighter" on resources.

Rory Archibald

Here's a function version:

Public Function ConcatIf(rngCrit As Range, varCriterion, rngConcat As Range, Optional strDelimiter As String = ", ")
   Dim varData, varCrit
   Dim lngrow As Long, lngCol As Long
   If rngCrit.Rows.Count <> rngConcat.Rows.Count Or rngCrit.Columns.Count <> rngConcat.Columns.Count Then
      ConcatIf = CVErr(xlErrRef)
   Else
      varCrit = rngCrit.value
      varData = rngConcat.value
      For lngrow = LBound(varCrit, 1) To UBound(varCrit, 1)
         For lngCol = LBound(varCrit, 2) To UBound(varCrit, 2)
            If StrComp(varCrit(lngrow, lngCol), varCriterion, vbTextCompare) = 0 Then
               ConcatIf = ConcatIf & strDelimiter & varData(lngrow, lngCol)
            End If
         Next lngCol
      Next lngrow
      If Len(ConcatIf) > 0 Then ConcatIf = Mid$(ConcatIf, Len(strDelimiter) + 1)
   End If
End Function

Open in new window

in a cell you would use it like:
=concatif(B2:I2,"x",$B$1:$I$1)
to concatenate values in B1:I1 where B2:I2 contains 'X'

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

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

Start Free Trial

AndresHernando

ASKER

Beautiful, rorya!!!!

Thanks, --Andres

Excel VBA concatenate table header txt for row/col intersections marked with &quot;X&quot;

Excel VBA concatenate table header txt for row/col intersections marked with "X"