Check list from above current cell and return blank if found

[JamesBowdidge]

Hi I have a list of numbers that sometimes repeat. I need to omit any value that already exists in the list from the cell above..

IE first number is column A second number is column B and where the formula is..

10 = 10
11 = 11
11 = ""
12 = 12
13 = 13
14 = 14
14 = ""
14 = ""
15 = 15

Im trying (and failing) to achieve this with the following formula..

=IF(LEFT(a2,19)=$I$1:I2,"",LEFT(a2,19))

I know the problem is with the =$I$1:I9 but I cant work out how to achieve the result I am looking for.. probably an array..

any ideas

 

[AlanY]

you can try the remove duplicate function in the data > data tool menu

 

[JamesBowdidge]

Thanks Alan,

Unfortunately this isnt an option for a couple of reasons.. mainly due to a couple of other things I am using the dataset for that require the dupes to remain.. I am adding to an existing workbook..

good idea tho

thanks

J

 

[AlanY]

ok, copy formula in B2 down, 1 indicates the first occurrence of the corresponding in Column A, then you can filter them out.

will that work?

Book1
	A	B
1
2	10	1
3	11	1
4	11	2
5	12	1
6	13	1
7	14	1
8	14	2
9	14	3
10	15	1
Sheet5

Cell Formulas
Range	Formula
B2	=COUNTIF($A$1:A2,A2)

 

[Fluff]

Or maybe

Excel 2013/2016
	A	B
2	10	10
3	11	11
4	11
5	12	12
6	13	13
7	14	14
8	14
9	14
10	15	15
Sheet1

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A$2:A2,A2)=1,A2,"")