How to check for multiple columns values and add the column name to a string based on the cell value

Question

In excelsheet I have 500 records with 50 or more columns. I need to check for all the columns for which the cell value is yes, add that column heading to a string separated by semicolon in the respective row. for eg :

Delhi   Jaipur  Basel   Colombia    Peris   London  Dubai   Munich   List
no      no      yes     yes         no      no      no      yes     Basel;Munich
yes     no      no      no          no      no      yes     no      Delhi;Dubai
no      no      no      no          no      no      no      no  
no      no      yes     yes         no      no      no      no      Basel;Colombia

Any input is appreciated.

Possible duplicate of [Concatenate top row cells if column below has 1](http://stackoverflow.com/questions/28679758/concatenate-top-row-cells-if-column-below-has-1/28680713#28680713). — , Mar 03 '16 at 19:36

score 0 · Answer 1 · answered Mar 03 '16 at 19:23

Try:

Dim answer as String
For i=2 to Range("A1").End(xlDown).Row
   answer=""
   For j=1 to 8
      If Cells(i,j)="yes" then
         answer=answer & Cells(1,j).Value & ";"
      End if
   Next j
   Cells(i,9)=answer
Next i

Note that this works for your example only, you need to check the column numbers.

How to check for multiple columns values and add the column name to a string based on the cell value

1 Answers1