Solved

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

Posted on 2011-03-13
6
860 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:AndresHernando
  • 4
  • 2
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35126030
Do you want the headers all joined up, or separated with a delimiter such as a space or comma?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35127087
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?
0
 

Author Comment

by:AndresHernando
ID: 35127242
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.
0
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35127307
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'
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35127341
Small tweak to return a null string if there are no matches:
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)
      Else
         ConcatIf = ""
      End If
   End If
End Function

Open in new window

0
 

Author Closing Comment

by:AndresHernando
ID: 35127925
Beautiful, rorya!!!!

Thanks, --Andres
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question