Deleting Duplicate Rows with Advance Filter 1

[Suggie]

Hello,

I'm trying to delete rows where there are duplicates. I've tried advance filter and it doesn't seem to work for my problem. Basically issue is:

Columns A through I contain data down to row 50, I then add in this weeks data which contains last weeks and this weeks data under row 50. The previous weeks will have explanations beside them in column J that I need to keep.

I think because there is a new column J with last weeks updates that prevents auto-filter working??

The column that could be used to identify duplicate records is C (this field will have duplicate references)

Any ideas? I was trying to think of a non vba way, but if this is all that works I be happy with any advice.

TIA,
Mark

 

[xlbo]

not really do-able with filters I'm afraid

Have a look at using the COUNTIF function to show you those rows which have 2+ records and then filter on that instead

Add the formula in col J under the explanations (ie for the new set of data only)
=if(COUNTIF($C$2:$C$150,$C$51)>1,"DELETE ME","")

adjust range refs to your likeing and then filter on col J for "DELETE ME" and then do so !

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

If you want to use Filter, you can select your data table, go to Data>Filter>Advanced Filter then tic the box beside "Unique records only". Copy n' Pasting somewhere else (different workbook, different sheet or elsewhere on the same sheet) will give you unique records only.

If you only want unique values in column C, select column C before going into Data>Filter...

John

Every generalization is false, including this one.

 

[Suggie]

Geoff,

What does the ,$C,$51 do? I can't get the formula to work.

When it does work this will do the trick.

TIA,
Mark


John,

The issue is the data in column J, I need to keep it.

 

[xlbo]

oops - that'll keep it on C51 - amend to:

=if(COUNTIF($C$2:$C$148,$C51)>1,"DELETE ME","")
this would be entered into J51 (ie under last week's comments) and copied down

This assumes you have 1 header row and then 49 rows of data for last week with comments in col J

It also assumes that you will have 98 more rows (49 for each week) to add. It will highlight those that are dupes in Col C so that you can then use autofilter and delete the rows


Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Suggie]

Pefect, that's sorted it.