Deleting duplicates in a range of rows

[Mkkl]

Dear people,

I have the following problem within Excel. I have a dataset which contains duplicate values within a column(lets say A).
I need to delete these duplicates in column A for a range of rows, where the range of the rows is based on column B.
So for a given value in column B, lets say 5 which is 6 rows long all beneath each other, I need to delete the duplicates in column A.

A B
1 2234
4 2234
5 2234
4 2234
2 2234
3 2234
5 2234
5 2657
8 2657
9 2657
10 2657
8 2657
9 2657
2 2657

So above the red numbers need to be removed, automatically, since we have about 8000 rows in our dataset.
Can someone give me a tip on how to approach this?

Greetings Michiel

[jraj1106]

Hi Michiel.

You just want to delete the duplicate value in col A. col B won't be affected? Just confused. You don't want the entire row to be deleted. Do you?

[Mkkl]

Actually I do want the whole row to be deleted. Sorry for the confusion.

I thought of the following idea: first take the column B and remove all duplicates from that column only and copy to column C. This way you will have all the unique values of column B. Then, write a routine that takes every value from column C one by one, searches for the range of rows which contain a value from column C in column B and searches for those range of rows in column A for duplicates to eventually delete them.

[Mkkl]

Is there someone who can think of a way to implement this idea in Excel?

[Fotis1991]

One other way could be this.

In a Column(let's say E) in E1, put this.

=IF(A1="","",A1)

In E2 and copy down, this one.

=IFERROR(INDEX(A$1:A$10,MATCH(1,INDEX((COUNTIF(E$1:E1,A$1:A$10&"")=0)*(A$1:A$10<>""),0),0)),"")

In F1 and copy down use this

=IFERROR(INDEX($B$1:$B$10,MATCH(E1,$A$1:$A$10,0)),"")

Now you have your new data.

If you don't want to have all these column, you can do this.

Highlight column E & F. >>>COPY

Choose another empty column(LET'S SAY H:I)>>Paste Special>>Only Values>>OK.

Delete columns A&B&E&F.

Highlight H:I >>Cut. Go to A1 Paste >>ok.

[arlu1201]

The data in column A is single digit but the data in column B is 4 digit. How do you say they are duplicate?

Also, i didnt understand this statement from post 1 - So for a given value in column B, lets say 5 which is 6 rows long all beneath each other, I need to delete the duplicates in column A.

[Lemice]

Hello,

I suggest making a helper column, something in Column C like this

Please Login or Register  to view this content.

Like in this sample file
Check duplicate.xls
Then, you can just find the row with 1 and delete it all!

Hope this help.

[martindwilson]

c1 =A1&"-"&B1
d1 =COUNTIF($C$1:C1,C1)=1
fill down
filter on false
delete visible rows