title: Excel function to build a JSON structure
body: <p>Am running <code>Office Professional Plus 2013</code> on <code>Windows 7</code>.</p>
  
  <p>Have a tree, depicted as an outline:</p>
  
  <pre><code>0   entity
  1   process
  1.1 movement
  1.1.1   passive movement
  1.1.1.1 diffusion
  1.1.1.1.1   brownian motion
  1.1.1.1.2   osmosis
  1.1.2   active movement
  1.1.2.1 locomotion
  1.2 interacting
  1.2.1   transporting
  1.2.1.1 chemical transport
  1.2.1.1.1   membrane transport
  1.2.1.1.1.1 passive transport
  1.2.1.1.1.2 active transport
  1.2.1.1.1.2.1   primary active transport
  1.2.1.1.1.2.2   secondary active transport
  1.2.1.1.1.2.2.1 symport enabled secondary active transport
  1.2.1.1.1.2.2.2 antiport enabled secondary active transport
  1.2.2   creating
  1.2.2.1 chemical synthesis
  1.2.2.1.1   biosynthesis
  1.2.2.1.1.1 transcription
  1.2.2.1.1.2 translation
  1.2.2.1.2   molecular complex formation
  1.2.2.2 birthing
  1.2.2.3 evolving
  1.2.2.4 planning
  1.2.2.5 learning
  1.2.2.6 reasoning
  1.2.2.7 predicting
  1.2.2.8 reproducing
  1.2.2.8.1   biological reproduction
  </code></pre>
  
  <p>And depicted as a hierarchy:</p>
  
  <pre><code>entity
      process
          movement
              passive movement
                  diffusion
                      brownian motion
                      osmosis
              active movement
                  locomotion
          interacting
              transporting
                  chemical transport
                      membrane transport
                          passive transport
                          active transport
                              primary active transport
                              secondary active transport
                                  symport enabled secondary active transport
                                  antiport enabled secondary active transport
              creating
                  chemical synthesis
                      biosynthesis
                          transcription
                          translation
                      molecular complex formation
                  birthing
                  evolving
                  planning
                  learning
                  reasoning
                  predicting
                  reproducing
                      biological reproduction
  </code></pre>
  
  <p>And depicted in Excel
  <img src="https://i.stack.imgur.com/CGrqM.png" alt="Excel image"></p>
  
  <p>The <code>Excel</code> depiction - as an image - represents a <code>JSON</code> structure. On the image, "n:" translates to "name": and "c:" translates to "children":. A row is an item with a name (n:). Row 1 says that all items on the following rows are its children. The presence of one or more children is designated with braces ([ ]). Some items do not have children - these are terminal nodes. With thanks to <code>@ron-rosenfeld</code> <a href="https://stackoverflow.com/questions/29720248/identify-a-child-cell-in-an-array">("Identify a child cell in an array")</a> we can identify the terminal nodes. Based on the structure of the tree, we can set all open braces ({) and open brackets ([). For terminal nodes, we also can set the closing brace (}) for the name. All other closing brackets (]) and closing braces (}) are a function of nested parent:child relationships.</p>
  
  <p><strong>How to use <code>Excel</code> function (or VB) to automate the correct closing of nested structures (i.e. the appropriate closing brackets and braces)?</strong></p>
  
  <p>For example, using cell locations, name on row 2 is opened at B2 and closed at AZ34. Name on row 19 is opened at R19 and closed at AJ19. Name on row 4 is opened at F4 and closed at AV10. To make the illustration easier to read, I'll repeat using the header symbols instead of cell locations. Name on row 2 is opened at 0{ and closed at 0}. Name on row 19 is opened at 8{ and closed at 8}. Name on row 4 is opened at 2{ and closed 2}. Now to the children. On row 18, name on 7{ (P18) has 2 children on rows 19 and 20. Children are opened on 7[ (AF18) and closed on 7] (AK20). On row 16, name on 6{ (N16) has 2 children on rows 17 and 18. Unlike the name on row 18, the name on row 17 has no child.</p>
  
  <p>We can do some math to check our work. The goal is that the sum of open braces minus closing braces is 0. The same is true for brackets.
  On the image and on the Google workbook "1_example_with_solution" (below), math begins on Row 36. BC38 verifies that the number of opening braces is balanced by the same number of closing braces. BC41 verifies that the number of opening brackets is balanced by the same number of closing brackets. In the full tree on Google workbook "2_example_without_solution" (below), the difference between open and close is 406 (rows 1257 and 1259). In other words, 406 brackets and braces - in the correct position - are needed to build a balanced and accurate <code>JSON</code> structure. It is possible to put brackets and braces in wrong positions and still have a balanced structure that is accepted by a <code>JSON validator</code> (I know this from experience). But the logic would be wrong, meaning that a graph of the nested structure would have incorrect parent:child relationships. Hence the desire to build an automated method in <code>Excel</code> to properly close all open conditions.</p>
  
  <p>An easier-to-read version of the <code>Excel</code> image is on <a href="http://tinypic.com/m/ix60jl/4" rel="nofollow noreferrer">tinypic</a>.</p>
  
  <p>The end-state <code>JSON</code> data structure for <code>Google workbook</code> "1_example_with_solution" is on <a href="http://pastebin.com/a3GCdjfZ" rel="nofollow noreferrer">pastebin</a>.</p>
  
  <p>Here are two <a href="https://docs.google.com/spreadsheets/d/1sRks-Tsex7i14U_5wJN1p1ypVMZopy1olbkg0ay5tgw/edit?usp=sharing" rel="nofollow noreferrer">Google Doc workbooks</a>. "1_example_with_solution" (green tab) has the data from the <code>Excel</code> image. The maximum width of the problem is 14 levels (0-13). To simplify the presentation on StackOverflow, I show only levels 0-8 in the <code>Excel</code> image and in "1_example_with_solution". A solution needs to work for levels 0-13. The full tree is in <code>Google workbook</code> "2_example_without_solution" (yellow tab), which also includes the calculation of the difference between open and closing conditions (rows 1257 and 1259).</p>