# Excel – Finding Unique Values in 3 Columns

microsoft-excel-2010

I have 3 columns in excel. I want to find unique values in the columns A, B and C.

So:

1. by looking in column A, I want to find the unique values that are not in B or C.
2. by looking in column B, I want to find unique values that are not in column A or C.
3. by looking in Column C, I want to find unique values that are not in column A or B.

This is what I put in column A

``````=IF(OR(MATCH(A3,\$B\$3:\$B\$631,0),(MATCH(A3,\$C\$3:\$C\$408,0))),"Match",A3)
``````

This is what I put in column B

``````=IF(OR(MATCH(B3,\$A\$3:\$A\$857,0),(MATCH(B3,\$C\$3:\$C\$408,0))),"Match",B3)
``````

This is what I put in column C

``````=(IF(OR(MATCH(C3,\$A\$3:\$A\$857,0),(MATCH(C3,\$B\$3:\$B\$631,0))),"Match",C3)
``````

This gives me duplicate values in the three columns I need them to be unique. Maybe I'm just making this very complicated. Any idea on how to do this?

The table example:

``````A   B   C
1   2   2
2   3   3
3   7   7
4   8   11
5   9   12
6   10  13
``````

In this instance, for I want the unique # in each column. So, for column A the unique values that are not in columns B or C are = to 1,4,5,6. In column B the unique values, that are not in A or C, are =8,9, 10. In column C the unique values, that are not in B or C are = 11, 12, 13. I want to make a formula in columns D, E, F for these three formulas.

A B C
1 2 2
2 3 3
3 7 7
4 8 11
5 9 12
6 10 13

In this instance, for I want the unique # in each column. So, for column A the unquie values that are not in columns B or C are = to 1,4,5,6. In column B the unique values, that are not in A or C, are =8,9, 10. In column C the unique values, that are not in B or C are = 11, 12, 13. I want to make a formula in columnc D, E, F for these three formulas.

You can use an array formula like the following in D1:

``````=IFERROR(INDEX(\$A\$1:\$A\$6,SMALL(IF(ISERROR(MATCH(\$A\$1:\$A\$6,\$B\$1:\$B\$6,0))*ISERROR(MATCH(\$A\$1:\$A\$6,\$C\$1:\$C\$6,0)),ROW(\$A\$1:\$A\$6)),ROW())),"")
``````

The formulae in the other columns are similar:

E1:

``````=IFERROR(INDEX(\$B\$1:\$B\$6,SMALL(IF(ISERROR(MATCH(\$B\$1:\$B\$6,\$A\$1:\$A\$6,0))*ISERROR(MATCH(\$B\$1:\$B\$6,\$C\$1:\$C\$6,0)),ROW(\$B\$1:\$B\$6)),ROW())),"")
``````

F1:

``````=IFERROR(INDEX(\$C\$1:\$C\$6,SMALL(IF(ISERROR(MATCH(\$C\$1:\$C\$6,\$A\$1:\$A\$6,0))*ISERROR(MATCH(\$C\$1:\$C\$6,\$B\$1:\$B\$6,0)),ROW(\$C\$1:\$C\$6)),ROW())),"")
``````

And from your example table, I get these:

If your formula starts on a different row than the first, the formula will need to be changed a bit because it uses `ROW()` which is dependent on the row the formula is placed in.

Note: To use the formula, you have to press and hold Ctrl+Shift then press Enter after inserting it.