Finding All Unique Values In One Column

Hey guys,

I have looked at a few forums and used a few formulas I found online however I cant find one that works. The problem sounds so simple in theory yet is incredibly hard for me to do. I have Column A in Excel with around 900k pieces of data. There are duplicates in Column A, I do not want to remove duplicates so only one version is left, I want to remove both occurrences of the duplicate so that in Column B I get all the data that is not anywhere in column A. I have used a few formulas I found online but I honestly could not get any of them to work. Would appreciate help in this as its really frustrating.

Thanks

Originally Posted by Tony Valko

Not real clear.

If this is your data what results do you expect?

Data Range

	A
1	1
2	1
3	1
4	2
5	3
6	3
7	4
8	5
9	5
10	5
11	-----

With 900K rows of data a formula solution will be slow to calculate.

Hey man, thanks for the reply. From your sample data I only want it to give me back the number 2 and number 4 since all others have a duplicate. @Alkey I am testing your formula now, thanks for putting it together. Will reply back here. Yeh these formulas do take a while to compute but I really need this data

Originally Posted by AlKey

Try this one

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

	A	B
1		Unique
2	GP7336	GP7336
3	GP4230	GP4230
4	GABC42	GABC42
5	GATB10	GATB10
6	GAEA73	GAEA73
7	GP7336	GARB73
8	GABC42	GAOB73
9	GARB73	GARB60
10	GAOB73	GASB27
11	GARB60	GASW00
12	GASB27	GATR31
13	GASW00
14	GATR31
15

Hey ALKey tried it now but it says cannot calculate the formula, there is a circular reference in the open workbook but the reference that cause it cannot be listed for you.

Thanks for this guys, I couldnt test if it works or not since my computer froze every time I tried but Im sure its correct

You could also try this non-array method, it'll calculate faster

Assume data runs in A2 down
In B2: =IF(A2="","",IF(COUNTIF(A:A,A2)>1,"",ROW()))
Copy down to last row of data

In C2: =IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))
Copy down just enough, ie until blanks are returned
---------------------------------
Any good? Wave it, hit the little star at the bottom left of my responses