title: Excel complex multi-column & multi-row concatenation using vba
body: <p>I am creating a spreadsheet full of products for an online shop and I really need some help with one of the fields.</p>
  
  <p>I have a parent product with several child products (think T-shirts with different sizes and colours).</p>
  
  <p>To keep things simple lets work with two colours (red &amp; blue) and two sizes (small &amp; large) - this gives 4 possible different combinations of product (ie small red, small blue, large red &amp; large blue)</p>
  
  <p>This data is listed on my sheet like so:</p>
  
  <pre><code>sku          colour      size       price
  t-shirt-rs   red         small      0
  t-shirt-rl   red         large      2
  t-shirt-bs   blue        small      0
  t-shirt-bl   blue        large      2
  </code></pre>
  
  <p>Now; here is the tricky part - on the parent product row I need to amalgamate all of the above data into one cell like this:</p>
  
  <pre><code>"t-shirt-rs[red#small[0;t-shirt-rl[red#large[2;t-shirt-bs[blue#small[0;t-shirt-bl[blue#large[2"
  </code></pre>
  
  <p>The "[" separates each new child-product name (sku) from their options and the "#" separates the child-product options then there is a further "[" to separate the price adjustment and then a ";" between each child-product</p>
  
  <p>Does the above even make sense??</p>
  
  <p>The 1st parent product that I have to upload has 4 options (size, colour, motif and material) and each option has up to 9 choices (4 sizes, 9 colours, 6 motifs &amp; 2 materials). I think I'm looking at (4x9x6x2=) 432 child products which will make for a <em>very</em> long manual concatenation job.</p>
  
  <p>I could do a simple =A2 &amp; "[" &amp; B2 &amp; "#" &amp; C2..... in the cell that I need but I fear that this will take forever.</p>
  
  <p>I'm hoping to be able to list the child products out as above (with copious amounts of copying and pasting :o) and then use vba to amalgamate into the single cell and add the ['s, #'s and ;'s in all of the right places.</p>
  
  <p>I suppose something like:</p>
  
  <pre><code>with the first row
  (add " symbol?) &amp; 1st cell &amp; "[" &amp; 2nd cell &amp; "#" &amp; 3rd cell &amp; "#" &amp; 4th cell .....
  move down one row
  same as above
  keep going until I run out of child products??
  add final " symbol
  </code></pre>
  
  <p>I'm new to VBA so don't really know where to start I'm afraid.
  Can anyone please point me in the right direction?</p>
  
  <p>Thanks,
  Alan</p>