Extract Unique List from a Single Column (Multiple Occurrences Appear only Once) – Skip Specific Value(s)
Return Unique List of Values from a Single Column, skipping Specific Values
Column A values appear either once (Unique) or multiple times (Duplicate) – these are extracted to appear ONLY ONCE, Skipping Specified Value(s).
In column D Extract Unique List of Names, except those specified in cell C2. Enter Non-Array formula in cell D2 & copy down.
Note: If cell D2 formula is in a different column, replace “D$1:D1” in the formula with that column (ex. if formula is in column J then update to “J$1:J1”); if cell D2 formula starts from a different cell, say D16 instead of D2, then replace “D$1:D1” with “D$15:D15”.
———————————————————————————————–
Extract Unique List from Column A, excluding cell C2 value
Cell D2 formula:
=IFERROR(INDEX(A$2:A$20,MATCH(0, INDEX( COUNTIF( D$1:D1, A$2:A$20)+ (A$2:A$20=$C$2)+ (A$2:A$20=””),) ,0)), “”)
Explanation – Cell D2 formula (non-Array formula):
1) COUNTIF(D$1:D1, A$2:A$20): counts values of column A in the preceding cells of the formula column (column D) as the formula gets copied down. It returns an array of 1s and zeros, with 1 indicating column A values which already occur in these preceding cells.
2) (A$2:A$20=$C$2): returns an array of TRUE and FALSE values – {TRUE;FALSE; FALSE;FALSE; FALSE;TRUE; FALSE;FALSE;…} – where TRUE indicates values in column A matching with C2 value and when TRUE is used with + it returns a 1.
3) COUNTIF(D$1:D1,A$2:A$20)+ (A$2:A$20=$C$2)+ (A$2:A$20=””): returns an array of 1s and zeros – {1;0;0;1; 0;1; 0;0; 1;0; 0;1; 1;…} – with zeros indicating non-blank column A cells which are not same as C2 and for column A values which have not already occurred in the preceding cells of column D. Index is used here to keep it a non-array formula. The 1st zero occurs in the 2nd position which means that cell A3 name “Mary” is not same as cell C2 and this value does not appear in the preceding cells in column D, hence cell D2 formula returns this name. Similarly the 2nd zero represents the cell A4 name of “Tim” and this appears in cell D3 formula. This is returned by using the functions MATCH & INDEX as explained in 4) below.
4) Using MATCH with the lookup_value of zero returns successive positions of non-blank column A cells which are not same as C2 and using this with the INDEX function – INDEX(A$2:A$100 – returns corresponding column A values.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Column E: Returns a Unique List of Values, except those specified in cell C2, by using a formula similar to column D, except that the column E formula is an Array formula (Ctrl+Shift+Enter) and shorter.
Column F: Uses the FREQUENCY function to return a Unique List of Values, except those specified in cell C2, and the formula is not dependent on previous cells of the column like column B / C formulas ie. cell D2 formula can be entered in any cell without requiring a change as mentioned in the abovementioned Note (If cell D2 formula is in a different column, replace “D$1:D1” in the formula with that column ….).
Columns H / I: Extract Unique List of Names, except those specified in range G2:G4. Enter Non-Array formula in cell H2 & copy down. Enter Array formula (Ctrl+Shift+Enter) in cell I2 and copy down. These formulas consider exclusions in the range G2:G4 in consolidation meaning that the formulas do not separately mention each cell value (G2, G3, G4) but consider the range G2:G4 as one to be excluded so as to not make the formula unwieldy even if a large number of multiple values are to be excluded.