title: Auto-concatenate in Excel
body: <p>I have a huge (~950 variables) survey response spreadsheet, made 2-4x larger than it needs to be because each item's individual response options are reported in separate columns.  E.g., If Question 2 is in cell A1 and has 3 response options, these are listed below the question in cells A2-C2, A3-C3, etc.  Note: Only one of A2-C2, etc. is filled w/ data for each observation.</p>
  
  <p>It seems clear that I <em>could</em> go through the spreadsheet manually and concatenate A2-C2 using code similar to this:</p>
  
  <pre><code>=CONCATENATE(IF(ISBLANK(A4),"",A4),IF(ISBLANK(B4),"",B4),IF(ISBLANK(C4),"",C4))
  </code></pre>
  
  <p>But, the above requires manually altering the code (questions have anywhere from 2-6 response options) and copying/pasting it through all observations for each of the 960 variables individually.  Because I'd like to finish this before dying of old age, I'd love to have some way of automating the above process.  </p>
  
  <p>It would be easy enough to go through and insert an identifier column (w/ no data but w/ some obvious 1st row name) after each variable's last response option so the code knows where to begin looking back for data to concatenate. The code would have to know to stop whenever it hits the previously concatenated result, and begin the current concatenation w/ the subsequent column. Once it hits a certain line (say, 60? I only have ~55 observations), it could just look for the next identifier column and repeat the process.</p>
  
  <p>Any ideas would be <em>much</em> appreciated.</p>
  
  <p>I need to check for blanks so as to not have extra spaces in the data (to aid future analysis).</p>
  
  <p>Hopefully the below will clarify the situation further. You're correct in your earlier comment; each question is followed by 55 rows of observations. For example:</p>
  
  <pre><code> | |   Q1   |         |        ||   Q2    |        || 
   |-|--------|---------|--------||---------|--------||
   |1|   1    |         |        ||         |    2   ||
   |2|        |    2    |        ||         |    2   ||
   |3|        |         |    3   ||         |    2   ||
   |4|        |    2    |        ||    1    |        ||
   |5|        |         |    3   ||         |        ||
   |6|   1    |         |        ||         |    2   ||
   |7|   1    |         |        ||    1    |        ||
   |8|        |         |    3   ||    1    |        ||
   |9|        |    2    |        ||         |    2   ||
  </code></pre>
  
  <p>There isn't currently a blank column after the last response option for each question, but (as mentioned in the initial post) I can easily throw one in.</p>