title: Excel to SQL and Handling Duplicate Values
body: <p>Lookup Table in Worksheet 1.</p>
  
  <pre><code>ID      CODE  REASSIGN(CODE)
  W12-05  AA    ZZ
  W12-05  BB    ZZ
  W14-01  CC    CC
  W14-03  AA    AA
  W15-01  DD    DD
  W15-01  DD    DD
  </code></pre>
  
  <p>ID and CODE are generated from the report</p>
  
  <p>REASSIGN(CODE) is what I am trying to accomplish. If there are multiple IDs (ex. W12-05) that are the same with different CODE values, then assign it to "ZZ" . However, if there are multiple IDs and they all have the same CODE values (ex. W15-01), then assign it to the same code value.</p>
  
  <p>There are 2 Worksheets (Worksheet 2 and Worksheet 3) that house the ID and different data associated with that ID. </p>
  
  <p>Data Table:</p>
  
  <pre><code>ID      LOOKUP REASSIGN(CODE)
  W12-05  ZZ
  W14-01  CC
  W14-03  AA
  W15-01  DD
  </code></pre>
  
  <p>How can I accomplish the REASSIGN(CODE) column in the first table? Something along the lines of: </p>
  
  <p>If IDs duplicate and multiple different CODEs, assign to ZZ.
  If IDs duplicate and same codes, assign to same CODE.</p>
  
  <p>There might be instances where if there certain multiple codes for duplicate IDs, it will need to assign to a specific CODE. "ZZ" is the default when I don't know where it should go and it can be handled by another department. Sometimes I know that code "EE" and code "FF" belong to one department and there is logic to assign it to "GG" for example.</p>
  
  <p>I want to combine all 3 Worksheets based on the ID column and I want all the data from Worksheet 2 and Worksheet 3 to be there, even if there isn't a match in IDs. Worksheet 1 will be used just as a lookup to pull in the REASSIGN(CODE).</p>
  
  <p>Please let me know if I need to explain further or if there are any questions. I am open to ideas and other solutions, since this is just the logic that came into my head. I am not sure if it's better to create another table for the REASSIGN(CODE) or if my logic is sound.</p>
  
  <blockquote>
    <p>Original Question - RESOLVED</p>
    
    <p>Combination of IF, REPLACE, and &amp; - I am happy to provide more details.</p>
    
    <p>I have 3 workbooks I am pulling data from and I do perform some data
    manipulation.</p>
    
    <p>Here are the formulas I currently have in my spreadsheet:</p>
    
    <p>Column: OB</p>
    
    <p>Formula: <code>=SUBSTITUTE(SUBSTITUTE([@[OBN]]," ",""),"-","")</code></p>
    
    <p>This cleans up the OBN column removing all hyphens and spaces to
    standardize it based on the data table.</p>
    
    <p>Column: DO</p>
    
    <p>Formula:</p>
  
  <pre><code>=IF(OR([@[DON]]="NA",ISBLANK(TRIM([@[DON]])),[@[DON]]="N/A",ISNA([@[DON]]),ISBLANK([@[DON]])),"",[@[DON]])
  </code></pre>
    
    <p>This makes the DO column blank if there is no value there or if it's
    NA or N/A, as that is how the report displays it. Otherwise, use the
    value of DON as DO.</p>
    
    <p>Column: OB+DO (This is the ID in the tables below)</p>
    
    <p>Formula:</p>
  
  <pre><code>=IF([@DO]="",[@OB],CONCATENATE([@OB],"-",[@DO]))
  </code></pre>
    
    <p>Concatenate columns OB and DO with a hyphen if DO contains a value
    that is not NA or N/A. Otherwise, just use OB.</p>
  </blockquote>