Extract Values which appear ONLY once (ie. Unique Values) from a Column
Only those values which appear ONCE in a Column are extracted.
Column A values appear either once (Unique) or multiple times (Duplicate) – only those values which appear ONCE are extracted in columns B;
Enter Non-Array formula in cell B2 and copy 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”.
———————————————————————————————–
Column A values appear either once (Unique) or multiple times (Duplicate) – only those values which appear ONCE are extracted in column B.
Cell B2 formula:
=IFERROR(INDEX(A$2:A$20, MATCH(0, INDEX( COUNTIF( B$1:B1, A$2:A$20)+ (COUNTIF( A$2:A$20, A$2:A$20)<>1),), 0)),””)
Explanation – Cell B2 formula (non-Array formula):
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) COUNTIF(A$2:A$20, A$2:A$20): returns an array of numbers and zeros – {2; 2;2; 1; 2;2; 2;0; 1;0; 0;0;0; 0;0; 0;0; 0;0} – the numbers indicate the number of times each column A cell value appears in the same column. Zeros indicate blank cells, 1 indicates that the value appears only once in the column, 2 indicates that a value appears twice, and so on. The 1st 2 indicates that cell A2 value (“Chris”) appears twice in column A, the next 2 in 2nd position indicates that cell A3 value (“Tracy”) appears twice in column A, and so on. Using “<>1” with these returns an array of TRUE and FALSE values – {TRUE;TRUE; TRUE;FALSE; TRUE;TRUE; TRUE; TRUE; FALSE; TRUE; TRUE;TRUE; TRUE; TRUE; TRUE;TRUE; TRUE; TRUE; TRUE} – where TRUE indicates values which are not equal to 1 and FALSE indicates 1s. The aim is to determine FALSE values which represent 1 meaning those values which appear only once in column A.
3) COUNTIF(B$1:B1, A$2:A$20)+ (COUNTIF(A$2:A$20, A$2:A$20)<>1): combining the formulas in 1) and 2) above returns an array of 1s and zeros – {1;1;1; 0;1;1;1; 1;0;1;1;1; 1;1;1;1; 1;1;1} – the zeros indicate the non-blank cell values in column A which appear only once and which have not appeared in preceding cells (as the formula is copied down). The INDEX function is used with this to keep it a non-array formula.
4) MATCH(0, INDEX( COUNTIF(B$1:B1, A$2:A$20)+ (COUNTIF( A$2:A$20, A$2:A$20)<>1),), 0): Using the MATCH function with lookup_value of zero determines the column A values, which occur only once in the column, and which do not appear in the preceding cells. In cell B2 this returns 4 which means that the 4th value of cell A5 appears only once in column A and it does not appear in the preceding cells on column B. Used with Index function “INDEX(A$2:A$20,” the corresponding values from column A are returned.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Column C: Extract only those values which appear ONCE in column A are extracted in column C. Enter Non-Array formula in cell C2 and copy down. The Logic of cell C2 formula is similar to cell B2 formula except that in cell C2 the COUNTIF is compared to 1. Similar to cell B2 formula, this formula also refers to previous cells of the column as per 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 F then update to “F$1:F1”); if cell C2 formula starts from a different cell, say C16 instead of C2, then replace “C$1:C1” with “C$15:C15”.
Column D: Extract only those values which appear ONCE in column A are extracted in column D. Enter Array formula in cell D2 and copy down. Uses the INDEX-SMALL functions with COUNTIF and MATCH functions to List Values which appear ONLY once (ie. Unique Values) in Column A. This 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 ….). This is an Array formula (Ctrl+ Shift+ Enter).
Column E: Extract only those values which appear ONCE in column A are extracted in column E. Enter Non-Array formula in cell E2 and copy down. Uses the AGGREGATE function with COUNTIF to List Values which appear ONLY once (ie. Unique Values) in Column A. This formula is not dependent on previous cells of the column like column B / C formulas ie. cell E2 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 ….).