Delete of duplicate records

[ChalkerL]

Hey guys,

I'm having trouble getting this piece of macro to work. I want to delete duplicate Id records in (Cell B12 : B124), but only those with "No" in the (Cell Z12 : Z124) will be deleted.

Table
B12 Z12
-------------
E1 Yes
E2 Yes
E3 Yes
E4 Yes
E1 No
E2 No
E3 No
E4 No

Expected results:
E1 Yes
E2 Yes
E3 Yes
E4 Yes

Please Login or Register  to view this content.

I was just wondering if it is possible to automatically delete duplicate records within the worksheet, even without using of a macro button.

[martindwilson]

without macro
just auto filter on column z=no
then delete rows
oops misread post
instead
in another spare column say col aa
in aa12
put
=countif ($b$12:$b$124,b12)>1 and drag down to aa124
select range a12:aa124 and apply auto filter
filter on
1 aa12:aa124 =true
then
2. z12:z124 =no
delete rows

[ChalkerL]

I might be easier to just use a macro that delete duplicate entries without having to always filter and delete.

[martindwilson]

er no! that took me about 30seconds!

[ChalkerL]

Hm.. But I'm actually creating this worksheet for another user, hence it might be easier with a macro button.

[martindwilson]

when do you want it to delete duplicates?
you could probably use that so it runs on work workbook open or before close and making sure it runs in thisworkbook and specified sheet

[ChalkerL]

Ok, then is there anyway i can display only 6 results.

I know that excel has this function where you display the top 10 results, but that is by the highest number of the cell. instead i want it to display top 6 results based on Cell Z being Yes then No, and not duplicates of the ID which is Cell B.

[martindwilson]

sorry i dont understand! how can you have top 6 when criteria is yes/no or do you mean only the first 6.

[ChalkerL]

If you dont mind looking at my excel, it is under shortlisted admin.

I'm actually trying to display the top 6 results.

[ChalkerL]

Sorry. I meant displaying only the first 6.

Currently i have sorted Cell Z in descending order. So the Yes would appear before the no.

And i want to display the first 6 results only.

[martindwilson]

which are the first 6 you want? not saying i can do it tho!
but if your code generates the non duplicate list then simply copy the first six
to another sheet. its geting too deep intp vba for me tho

[ChalkerL]

Hmm .. Thanks.

All the worksheet is currently sorted based on the cell Z with the Yes being the main priority, then the No.

Cause if the Yes doesn't display the No will automatically go up based on the filter.

So with the listing that has been sorted, I'm trying to display only the first 6 after the sorting.

[ChalkerL]

but if your code generates the non duplicate list then simply copy the first six

but it's hard to capture the cell number cause the cell number would jumble up after sorting. So it's hard to predict which cells to copy

[ChalkerL]

Can i incorporate your code into my formula

Please Login or Register  to view this content.

At the end, i check for duplicates

Please Login or Register  to view this content.