# Extract a Sorted List of Unique values in a column

Return Sorted Unique List of Values from Column having only Text or only Numbers

Sort Column values in Ascending / Descending Order, Retain Duplicates or Extract only Once (ie. Uniques)

Column F: Extracts Uniques & Sorts column E values in Ascending order – column E can have ONLY Numbers. Enter Array formula (Ctrl+Shift+Enter) in cell F2 and copy down.

Note: If cell F2 formula is in a different column, replace “F\$1:F1” in the formula with that column (ex. if formula is in column D then update to “D\$1:D1”).

———————————————————————————————–

In Column F, return Unique & Sorted column E values in Ascending order – column E can have ONLY Numbers – Array formula.

Cell F2 formula:

=IFERROR( SMALL( IF( COUNTIF( F\$1:F1, E\$2:E\$20)+ (E\$2:E\$20=””)=0, E\$2:E\$20), 1), “”)

Explanation – Cell F2 formula (Array formula):

1) COUNTIF(F\$1:F1, E\$2:E\$20): counts values of column A in the preceding cells of the formula column (column F) as the formula gets copied down. It returns an array of 1s and zeros, with 1 indicating column E values which already occur in these preceding cells.

2) IF(COUNTIF( F\$1:F1, E\$2:E\$20)+ (E\$2:E\$20=””)=0, E\$2:E\$20): this combines the 1) above with (E\$2:E\$20=””) which returns TRUE for blank cells and this TRUE becomes 1 when used in calculation. As this is copied down, it returns an array of 1s and zeros, where 1 indicates column E values which already occur in preceding cells or for blank cells. The aim is to match with zero values and return non-blank column E cells which have not already appeared in preceding cells of column F – {111;333; 555;777; 333;111; 333;FALSE; 66; FALSE;…}.

3) SMALL(IF(COUNTIF( F\$1:F1, E\$2:E\$20)+ (E\$2:E\$20=””)=0, E\$2:E\$20), 1): Using the SMALL function with column E values returned in 2) above will return the SMALLEST column E value which does not occur in the preceding cells of column F. IFERROR function is used to return blank cells after all column E values have been accounted for once.

———————————————————————————————–

Related Formulas – illustrated in the Ebook of Excel Formulas.

Column B: Cell B2 formula uses INDEX, MATCH, SMALL & COUNTIF functions and is entered as an Array formula (Ctrl+Shift+Enter). It Retains Duplicates & Sorts column A values in Ascending order. However, column A can have either ONLY Text or ONLY Numbers. The formula does not refer previous cells in the same column B.

Column C: Cell C2 formula uses INDEX, MATCH, SMALL & COUNTIF functions and is entered as an Array formula (Ctrl+Shift+Enter). It Extracts Uniques & Sorts column A values in Ascending order. However, column A can have either ONLY Text or ONLY Numbers. This formula also refers to previous cells in the column – if cell C2 formula is in a different column, replace “C\$1:C1” in the formula with that column (ex. if formula is in column D then update to “D\$1:D1”).

Column G: Cell G2 formula uses AGGREGATE & COUNTIF functions and is entered as an Non-Array / Normal formula. It Extracts Uniques & Sorts column E values in Ascending order. However, column A can have ONLY Numbers. This formula also refers to previous cells in the column – if cell G2 formula is in a different column, replace “G\$1:G1” in the formula with that column (ex. if formula is in column D then update to “D\$1:D1”).

Column I: Cell C2 formula uses the SMALL function and is entered as an Non-Array / Normal formula. It Retains Duplicates & Sorts column E values in Ascending order. However, column E can have ONLY Numbers.

Scroll to top