title: Extract unique values from a range in excel while disregarding specific values
body: <p>I originally had an Excel spreadsheet which was used to record values of JOB LOT IDs (>10000). I used the following array formula - </p>
  
  <pre><code>=INDIRECT(TEXT(MIN(IF(($C$3:$S$52&lt;&gt;"")*(COUNTIF($V$3:V3,$C$3:$S$52)=0),ROW($3:$52)*100+COLUMN($C$S),7^8)),"R0C00"),)&amp;""
  </code></pre>
  
  <p>Data:</p>
  
  <pre><code>| pallet    | Lot#      | Lot#      | Lot#      | Lot#      | Lot#      |
  |--------   |-------    |-------    |--------   |-------    |--------   |
  | 1         | 12345     | 12346     | 12345     | 12347     | 123456    |
  | 2         | 12345     | 12346     | 12348     | 12348     | 12343     |
  | 3         | 12345     | 12347     | 123456    | 12348     | 12348     |
  </code></pre>
  
  <p>This worked fine if the cells in that range all represented the JOB LOT IDs.
  It would record the unique LOT #'s as I copied this into the result range and coupled with the counting formula </p>
  
  <pre><code>(IF(LEN(V4)&gt;0,COUNTIF($C$3:$S$52,V4),"")
  </code></pre>
  
  <p>in the adjacent cell. It returned: </p>
  
  <pre><code>Unique  
  Value  Count
  ______ _____
  12345    4   
  12346    2   
  12347    2  
  123456   2 
  12348    4 
  12343    1
  </code></pre>
  
  <p>Unfortunately, the scope of the job and spreadsheet changed such that the spreadsheet needed to include columns before each JOB LOT cell to record the Case# of the JOB LOT.</p>
  
  <p>What I need help with is figuring out how to disregard the case# data, which will always be between 1 and 451, and only count the unique JOB LOT IDs, which will always be > 100000. Resulting in only the unique list of Job Numbers. Using the same array formula with the added column for Case#, the Case Numbers are also listed, when they are not needed or wanted.</p>
  
  <pre><code>| pallet    | case#     | Lot#      | case#     | Lot#      | case#     | Lot#      | case#     | Lot#      | case#     | Lot#      |
  |--------   |-------    |-------    |-------    |-------    |-------    |--------   |-------    |-------    |-------    |--------   |
  | 1         | 1         | 12345     | 45        | 12346     | 356       | 12345     | 6         | 12347     | 7         | 123456    |
  | 2         | 3         | 12345     | 35        | 12346     | 212       | 12348     | 23        | 12348     | 200       | 12343     |
  | 3         | 54        | 12345     | 34        | 12347     | 450       | 123456    | 345       | 12348     | 367       | 12348     |
  </code></pre>
  
  <p>The result is </p>
  
  <pre><code>Unique
  Value   Count
  ______  _____
  12345     4  
  45        1
  12346     2 
  356       1
  6         1
  12347     2 
  7         1  
  123456    2 
  35        1 
  212       1 
  12348     4 
  23        1 
  200       1 
  12343     1 
  34        1 
  450       1 
  345       1 
  367       1
  </code></pre>
  
  <p>Any Suugestions?
  Thanks.</p>