Formula to ignore field if cell contains a value

[Panthera]

Hi,

I've been stuck on this for a while and need your help to progress. I have around 7000 rows of duplicate values and I'm trying to filter out certain elements. I want to retain all duplicate values if one of the UserNames have a Value in colum A...

As long as the "UserName" column is filtered A-Z I can fill in the blank fields with the help of the following formula and it will highlight the rows I need to delete by giving a FALSE statement.

=IF(B2=B1,A1)

Using this formula in the empty cells in column A will mark A2, A3 with Value1, A4, A5, A6 with FALSE and A8 with Value2, so all good so far as I can then delete the FALSE rows... My problem is that I have 7000 rows and copying the formula into only the empty rows will take forever.

Is there a way to get the formula to ignore and retain the value in A1 and A7 if I were to apply the formula to all of column A? OR is there a better approah to mark UserName 2 and 3 for deletion?

Value1	UserName 1
	UserName 1​
	UserName 1​
	UserName 2​
	UserName 2​
	UserName 3​
Value2​	UserName 4​
	UserName 4​

 

[Peter_SSs]

copying the formula into only the empty rows will take forever.

Try this with a copy of your data

Select the whole of column A by clicking its heading label.
Press F5 -> Special... -> Blanks -> OK
Enter your =IF(B2=B1,A1) formula in the formula bar and press Ctrl+Enter

Filter FALSE & delete

 

[Panthera]

@Peter_SSs
Man, gotta love the simplicity of that. I had started to look in unlocking cells and protecting the sheet. Thank you very much

 

[Peter_SSs]

@Peter_SSs
Man, gotta love the simplicity of that. I had started to look in unlocking cells and protecting the sheet. Thank you very much

You're welcome. Glad it worked for you and thanks for the follow-up.