title: How to create an array from a range that contains the header row, first column, and the intersecting data in Excel?
body: <p>I have a range in Excel where Column A is an Account number, Row 1 is the Divisions and each intersection has an amount (or is null).  For example,</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column A</th>
  <th>Column B</th>
  <th>Column C</th>
  <th>Column D</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td></td>
  <td><strong>DIV10</strong></td>
  <td><strong>DIV20</strong></td>
  <td><strong>DIV30</strong></td>
  </tr>
  <tr>
  <td><strong>1000</strong></td>
  <td></td>
  <td>$527</td>
  <td>$333</td>
  </tr>
  <tr>
  <td><strong>1100</strong></td>
  <td>$1,250</td>
  <td>$221</td>
  <td></td>
  </tr>
  <tr>
  <td><strong>1200</strong></td>
  <td></td>
  <td></td>
  <td>$126</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I would like to convert this data into 3 columns, where Col A is the division, Col B is the Acct and Col C is the amount.  The example result would be</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column A</th>
  <th>Column B</th>
  <th>Column C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td><strong>DIV20</strong></td>
  <td><strong>1000</strong></td>
  <td>$527</td>
  </tr>
  <tr>
  <td><strong>DIV30</strong></td>
  <td><strong>1000</strong></td>
  <td>$333</td>
  </tr>
  <tr>
  <td><strong>DIV10</strong></td>
  <td><strong>1100</strong></td>
  <td>$1,250</td>
  </tr>
  <tr>
  <td><strong>DIV20</strong></td>
  <td><strong>1100</strong></td>
  <td>$221</td>
  </tr>
  <tr>
  <td><strong>DIV30</strong></td>
  <td><strong>1200</strong></td>
  <td>$126</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Is there a function (or combination of functions) that could accomplish this task?  This is something that the data gets overridden monthly, so having a formula in one cell to spill the results to automatically would be ideal.</p>