Check list from above current cell and return blank if found

JamesBowdidge

New Member
Joined
Nov 8, 2018
Messages
46
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
you can try the remove duplicate function in the data > data tool menu
 
Upvote 0
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
 
Upvote 0
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
AB
1
2101
3111
4112
5121
6131
7141
8142
9143
10151
Sheet5
Cell Formulas
RangeFormula
B2=COUNTIF($A$1:A2,A2)
 
Upvote 0
Or maybe


Excel 2013/2016
AB
21010
31111
411
51212
61313
71414
814
914
101515
Sheet1
Cell Formulas
RangeFormula
B2=IF(COUNTIF($A$2:A2,A2)=1,A2,"")
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top