title: Excel: Edit Column Data by Evaluating Data from Other Column with Matching Data from Third Column
body: <p>We have data from our system that contains demographics data for insurance choices. The employees could have selected insurance for themselves, insurance for them and their children, insurance for them and their spouse, or insurance for their whole family (them, their spouse, and their children). Traditionally we would group them by total number (Employee, Employee+One, or Family), and we would not care if the combination included children or spouses, but the carrier wants it differently. </p>
  
  <ul>
  <li>Employee</li>
  <li>Employee + Spouse</li>
  <li>Employee + Child</li>
  <li>Employee + Children</li>
  <li>Family </li>
  </ul>
  
  <p>We need to retain the number of rows because each row if for a different person, but Member IDs are shared between the employee and any dependents. </p>
  
  <p>I am trying to find a way using a formula, preferably because then it can be used by more people, to look at all the same <strong>Member IDs</strong> (could be 1 to infinity - the highest I've seen so far is 7) and then another column that says either <strong>self, Spouse, or Child</strong>, and add one of the above designations depending on the outcome.</p>
  
  <p>So essentially I would have an additional column (let's say Column H) that would say Employee, Employee + Spouse, Employee + Child, Employee + Children, or Family depending on what combination of self, Spouse, and Child were found in Column B for the matching Column A IDs. </p>
  
  <p>Below is an example of the data. I've removed columns that aren't relevant (email address, home address, social, etc.) that are included in the actual data, and I have changed all of the names and Member IDs, but the data with which we are working is very similar. </p>
  
  <p>Most of the solutions I have seen result in one row with the concatenated data, which would still need to be parsed into the appropriate designations, but I need it to be repeated for all rows. For instance, in the example below:</p>
  
  <ul>
  <li>Row 2 and Row 3 would both have Employee + Spouse</li>
  <li>Row 4 would have Employee</li>
  <li>Rows 5-7 would have Employee + Children</li>
  <li>Row 8 would have Employee</li>
  <li>Rows 9-15 would have Family</li>
  <li>Row 16 and Row 17 would have Employee + Child</li>
  </ul>
  
  <p><a href="https://i.stack.imgur.com/Puior.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Puior.png" alt="Excel Data Example"></a></p>