title: Concatenate values based on multiple criteria
body: <p>I have the following dataset</p>
  
  <pre><code>Key ID  Status 1    Status 2    Order ID
  1   A1  FALSE        TRUE     1234-USF-0025
  1   A1  FALSE        TRUE     1234-USF-0026
  1   A1  FALSE        TRUE     1234-USF-0027
  2   A1  TRUE         TRUE     1234-USF-0025
  2   A1  TRUE         TRUE     1234-USF-0026
  2   A1  TRUE         TRUE     1234-USF-0027
  3   A1  FALSE        TRUE     1234-USF-0025
  3   A1  FALSE        TRUE     1234-USF-0026
  3   A1  FALSE        TRUE     1234-USF-0027
  4   A2  TRUE         TRUE     1234-USF-0028
  4   A2  TRUE         TRUE     1234-USF-0029
  4   A2  TRUE         TRUE     1234-USF-0030
  5   A3  TRUE         TRUE     1234-USF-0031
  5   A3  TRUE         TRUE     1234-USF-0032
  5   A3  TRUE         TRUE     1234-USF-0033
  6   A4  TRUE         TRUE     1234-USF-0034
  6   A4  TRUE         TRUE     1234-USF-0035
  6   A4  TRUE         TRUE     1234-USF-0036
  </code></pre>
  
  <p>I need the following</p>
  
  <pre><code>Order ID        ID  TRUE    FALSE
  1234-USF-0025   A1   2       1,3
  1234-USF-0026   A1   2       1,3
  1234-USF-0027   A1   2       1,3
  1234-USF-0028   A2   4  
  1234-USF-0029   A2   4  
  1234-USF-0030   A2   4  
  1234-USF-0031   A3   5  
  1234-USF-0032   A3   5  
  1234-USF-0033   A3   5  
  1234-USF-0034   A4   6  
  1234-USF-0035   A4   6  
  1234-USF-0036   A4   6  
  </code></pre>
  
  <p>In the second table (the one I need), each <code>Order ID</code> is listed next to the corresponding <code>ID</code>. Although  <code>A1</code> is listed 9 times in the original dataset, there are only 3 unique <code>Order IDs</code> in total for <code>A1</code>. However, <code>A1</code> is also associated with 3 different Keys.</p>
  
  <p>The goal is to concatenate the <code>Keys</code> for each <code>Order ID</code> and <code>ID</code> combination, where both <code>Status 1</code> and <code>Status 2</code> are <code>TRUE</code> and list them in the <code>TRUE</code> column. For those <code>Order ID</code> and <code>ID</code> combinations where at least one <code>Status</code> is <code>FALSE</code>, the <code>Keys</code> should be listed under the <code>FALSE</code> column.</p>
  
  <p><strong>What I've tried</strong></p>
  
  <ol>
  <li>I tried starting with just the <code>TRUE</code> column, using INDEX-MATCH as an array formula, and although I know the below formula would not work for my desired end goal, I was trying to start small and build upon the formula. Unfortunately, my knowledge of arrays is limited, I'm not sure how to proceed because I don't understand why it returns what it does or how to reach my goal from this point.</li>
  </ol>
  
  <p><code>=INDEX($C$2:$C$19,MATCH(1,($H2 = $B$2:$B$19) * ($G2 = $E$2:$E$19)))</code> </p>
  
  <p><a href="https://i.stack.imgur.com/UQak4.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UQak4.jpg" alt="enter image description here"></a></p>
  
  <ol start="2">
  <li>Next I tried to break the pieces apart in the original dataset, but got stuck on how to proceed. I think this is the easier solution, but I can't figure out how to concatenate based on the required criteria. </li>
  </ol>
  
  <p>TRUE: <code>=IF(AND($C2=TRUE,$D2=TRUE),$A2,"")</code></p>
  
  <p>FALSE: <code>=IF(OR($C2&lt;&gt;TRUE,$D2&lt;&gt;TRUE),$A2,"")</code>
  <a href="https://i.stack.imgur.com/JtYjy.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JtYjy.jpg" alt="enter image description here"></a></p>
  
  <p><strong>Notes:</strong></p>
  
  <ul>
  <li>An <code>ID</code> is associated with at least one <code>Key</code>, but can have more</li>
  <li><code>Order ID</code> can repeat for the same <code>ID</code> but only for different <code>Keys</code> for that <code>ID</code>.</li>
  </ul>
  
  <p>I am open to a <code>VBA</code>, <code>Python</code> or <code>R</code> based solution as well, but not sure how to even start a script for this task, so I've been focusing on <code>Excel</code>. </p>