title: VBA User Defined Function for "Concatenate If" by rows
body: <p>I have some data of whether or not a particular "service" (e.g. Bird Survey) has been performed for a particular site, with a "yes" or "no" for each service.</p>
  
  <p>E.G. </p>
  
  <ul>
  <li><p>Column A contains site names, say A, B, C, D and E with the title "Site Name" in A1 then "A" in A2 etc. </p></li>
  <li><p>Column B contains "Bird Survey" in B1, then either a "yes" or "no" for B2-B6. </p></li>
  <li><p>Ditto for other services in columns C, D and E, lets say "Bat Survey", "LVI" and "Land Registry" respectively. </p></li>
  <li><p>In F I want to concatenate the service names for each row containing a "yes". E.G. lets say the values for B2,C2,D2 and E2 are "yes", "yes", "no" and "yes", I want F2 to contain Bird Survey, Bat Survey.</p></li>
  </ul>
  
  <p>As I understand it there are no native functions in excel that can do this, and so I've been trying to create a user defined function in VBA. I've tried two approaches</p>
  
  <ul>
  <li><p>one based on feeding two ranges (column names and row of "yes/no"'s) into the UDF and then combining these into an array to apply some sort of lookup criteria </p></li>
  <li><p>and another returning column letter from one the yes/no range only then selecting from the column names based on column letter. </p></li>
  </ul>
  
  <p>I've not been able to get either to work though. Note that in the end I need to create a UDF that works for a varying number of services, they won't be pre-defined as in this example.</p>
  
  <p>Any suggestions?</p>
  
  <p>Many thanks in advance.</p>