delete duplicate record but only determine 1 column data

[AskExcel]

Hi,
i want to delete duplicate data in column code with disregard to other
column data. if i use data -> filter -> advanced filter, this cannot solve my
problem. for example
i just want to delete duplicate record in column code by disregard the
column misc

code misc
1200 40
1300
1300 76
1300
1500
1500 12

after
code misc
1200 40
1300
1500 12

if i use the data -> filter -> advanced filter
the result is
code misc
1200 40
1300
1300 76
1500
1500 12

[wjohnson]

Sort the column you want to find the duplicate data, (assume Column A)
Insert a Column or go to a empty column, (column B for this example).
Make sure you data you want to check for duplicates starts at Cell A2.
In Cell B2 enter the following formula: =IF(A2=A1,"Dup","Not Dup"), and copy it down. Now you should be able to filter on the DUP and just delete those values.

[Max]

Perhaps there's some inconsistency in your post as to the desired results ..

> after
> code misc
> 1200 40
> 1300
> 1500 12

A duplicate means the 2nd, 3rd, 4th, ... instances
after the first instance of the item (the unique)

The last line in the desired results above:
> 1500 12
should not show, since the "1500" associated with the 12 under "misc" col
is the 2nd instance, i.e. it is a duplicate instance to be deleted

Hence the "actual" results should look like:

> 1200 40
> 1300
> 1500

If so, here's a non array formulas play which can retrieve the "actual"
results

Assuming source data is in cols A and B, from row2 down

Put in C2:
=IF(ISERROR(SMALL($E:$E,ROW(A1))),"",IF(INDEX(A:A,MATCH(SMALL($E:$E,ROW(A1))
,$E:$E,0))=0,"",INDEX(A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0))))

Copy C2 to D2

Put in E2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))

Select C2:E2, fill down until the last row of data in col A
Cols C and D will return the results, all neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AskExcel" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> i want to delete duplicate data in column code with disregard to other
> column data. if i use data -> filter -> advanced filter, this cannot solve
my
> problem. for example
> i just want to delete duplicate record in column code by disregard the
> column misc
>
> code misc
> 1200 40
> 1300
> 1300 76
> 1300
> 1500
> 1500 12
>
> after
> code misc
> 1200 40
> 1300
> 1500 12
>
> if i use the data -> filter -> advanced filter
> the result is
> code misc
> 1200 40
> 1300
> 1300 76
> 1500
> 1500 12
>

[Ron Coderre]

With your data in Columns A and B

D1: Criteria
D2: =COUNTIF(A$1:A2,A2)>1

Data>Filter>Advanced Filter>
List Range: (Select from A1 to the last item in col_B)
Criteria: $D$1:$D$2
Click the [OK] button

(That filter will hide the 1st instance of items in Col_A and only display
records with duplicates.)

Select from A2 through the bottom of the displayed list.
Edit>Delete>Row

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"AskExcel" wrote:

> Hi,
> i want to delete duplicate data in column code with disregard to other
> column data. if i use data -> filter -> advanced filter, this cannot solve my
> problem. for example
> i just want to delete duplicate record in column code by disregard the
> column misc
>
> code misc
> 1200 40
> 1300
> 1300 76
> 1300
> 1500
> 1500 12
>
> after
> code misc
> 1200 40
> 1300
> 1500 12
>
> if i use the data -> filter -> advanced filter
> the result is
> code misc
> 1200 40
> 1300
> 1300 76
> 1500
> 1500 12
>