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

**Related Links:**

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

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

**Column B**: Retains Duplicates & Sorts column A values in Ascending order - column A can have either ONLY Text or ONLY Numbers - Array formula;

**Column C**: Extracts Uniques & Sorts column A values in Ascending order - column A can have either ONLY Text or ONLY Numbers - Array formula;

**Column F**: Extracts Uniques & Sorts column E values in Ascending order - column E can have ONLY Numbers - Array formula;

**Column G**: Extracts Uniques & Sorts column E values in Descending order - column E can have ONLY Numbers - NonArray formula;

**Column I**: Retains Duplicates & Sorts column E values in Ascending order - column E can have ONLY Numbers - NonArray formula;

Sort Column Values consisting of i) Only Text; or ii) Only Numbers; in Ascending / Descending order - skip blank cells. Note: A column can consist of EITHER only Text Values OR only Numbers.

Extract Sorted Unique (or retain Duplicates) List of column Values, in Ascending / Descending order.

In column B, the formula is to be entered in cell B2 & copied down. Similarly for other column formulas.

To sort numbers in a column, retaining duplicates, just use the SMALL function as illustrated in column I.

**Note**: 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"); do similarly for cell F2 & G2 formulas.

-----------------------------------------------------------------------------------------------

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

**"Open Image in New Tab" for a full and clear view.**

**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.

**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 - refer the abovementioned Note - 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"); do similarly for cell F2 & G2 formulas.

**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 - refer the abovementioned Note - if cell C2 formula is in a different column ...

**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.

**Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas**.