Check columns for data match...

[Guest]

Using XL 2000

I have a worksheet which contains 10 columns A:J and over 500 lines.
The data in each cell in the Column range E:J contains either a TRUE or FALSE.

Row 1 contains header labels for each column.

I’m looking for a routine to loop through each column, per line and where a
TRUE exists in that column, and ( where more than one TRUE is found)
concatenate the label name in to column K of the same row being checked.

Hope this makes sense.
Ideas appreciated. Thanks, Paul

 

[Guest]

You can do this as a worksheet formula instead of code; below is the formula
for K2, which could then be copied down for each row in your list:

=IF(E2,E$1,"")&IF(F2,F$1,"")&IF(G2,G$1,"")&IF(H2,H$1,"")&IF(I2,I$1,"")&IF(J2,J$1,"")

If you want spaces between the label names:
=TRIM(IF(E2,E$1&" ","")&IF(F2,F$1&" ","")&IF(G2,G$1&" ","")&IF(H2,H$1&"
","")&IF(I2,I$1&" ","")&IF(J2,J$1&" ",""))

 

[Dave Peterson]

How about a formula:

=IF(E2,$E$1,"")&IF(F2,$F$1,"")&IF(G2,$G$1,"")&IF(H2,$H$1,"")
&IF(I2,$I$1,"")&IF(J2,$J$1,"")
(all one cell)

or if you want spaces between values:
=TRIM(IF(E2,$E$1,"")&" "&IF(F2,$F$1,"")&" "&IF(G2,$G$1,"")&" "&IF(H2,$H$1,"")
&" "&IF(I2,$I$1,"")&" "&IF(J2,$J$1,""))
(still all one cell)

 

[Guest]

Thanks for your ideas.
looking at my options again, I need to resolve this programatically.
I think my answer is based around a column loop through routine.
Just not sure of the code.
Paul

 

[Guest]

If it needs to be done programatically, then you could do it like this:
Dim ListLength as Integer, RowCounter as Integer, CellVal as String
ListLength = Range("A1").CurrentRegion.Rows.Count
For RowCounter = 2 to ListLength
With Range("K" & RowCounter)
.Formula = "=IF(E2,$E$1,"""") & IF(F2,$F$1,"""") & IF(G2,$G$1,"""") &
IF(H2,$H$1,"""") & IF(I2,$I$1,"""") & IF(J2,$J$1,"""")"
CellVal = .Value
.Value = CellVal
End With
Next RowCounter