User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active


Return Unique List of values from a Single Column - Multiple Occurrences to Appear only Once



Extract Unique List of Values from a Single Column


Column A values appear either once (Unique) or multiple times (Duplicate) - these are extracted to appear ONLY ONCE in a column;


In column B, the formula (non-array) is to be entered in cell B2 & copied down;


Note: If cell B2 formula is in a different column, replace "B$1:B1" in the formula with that column (ex. if formula is in column F then update to "F$1:F1"); if cell B2 formula starts from a different cell, say B16 instead of B2, then replace "B$1:B1" with "B$15:B15";




Extract Unique List from Column A


Unique List - Single Column 


Cell B2 formula:


=IFERROR(INDEX(A$2:A$20, MATCH(0, INDEX( COUNTIF( B$1:B1, A$2:A$20)+ (A$2:A$20="") ,), 0)), "") 


Explanation - Enter formula (non-array) in cell B2 and copy down:


1) COUNTIF(B$1:B1, A$2:A$20): counts values of column A in the preceding cells of the formula column (column B) 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) INDEX(COUNTIF(B$1:B1,A$2:A$20)+ (A$2:A$20=""),): this combines the above with (A$2:A$20="") which returns TRUE for blank cells and this TRUE becomes 1 when used in calculation. Index is used to keep it a non-array formula. As this is copied down, it returns an array of 1s and zeros, where 1 indicates column A values which already occur in preceding cells or for blank cells. The aim is to match with zero values which return non-blank column A cells which have not already appeared in preceding cells of column B. This is the lookup_array argument of the MATCH function.


3) MATCH(0,INDEX( COUNTIF(B$1:B1, A$2:A$20)+ (A$2:A$20=""),) ,0): The Match function is used with lookup_value of zero to determine column A values which do not appear in the preceding cells and this also skips blank cells, as explained above. Used with the INDEX function - "INDEX(A$2:A$20," - the unique column A values are returned. 





Related Formulas - illustrated in the Ebook of Excel Formulas.



Column C: Returns a Unique List of Values by using a formula similar to column B, except that the column C formula is an Array formula and shorter.


Column D: Uses the FREQUENCY function to return a Unique List of Values 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 B2 formula is in a different column, replace "B$1:B1" in the formula with that column...).