Delete duplicate numbers

N

Noemi

How do I delete duplicate numbers from a row using a formula rather then
finding each one seperately.

Thanks
 
M

Max

One way to extract the uniques list using non-array formulas

Assuming numbers listed in A1 down

Put in B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

Put in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",
INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1:A1)),B:B,0)))

Select B1:C1, copy down till the last row of data in col A

Col C will return the list of unique numbers in col A,
neatly bunched at the top
 
M

Max

well .. if the numbers are listed in row1, in A1 across: A1, B1, C1 ...
then:

Put in A2:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",COLUMN()))

Put in A3
=IF(ISERROR(SMALL(2:2,COLUMNS($A$1:A1))),"",
INDEX(1:1,MATCH(SMALL(2:2,COLUMNS($A$1:A1)),2:2,0)))

Select A2:A3, copy across till the last col of data in row1

Row3 will return the list of unique numbers in row1,
neatly bunched to the left (in A3, B3, C3 ...)

"deleting duplicates" is equivalent to "extracting uniques"
 
B

Biff

"deleting duplicates" is equivalent to "extracting uniques"

Max, are you a politician by any chance?

<vbg>

Biff
 
M

Max

"deleting duplicates" is equivalent to "extracting uniques"
Max, are you a politician by any chance?
<vbg>
Click to expand...

Doubt I'd be able to survive the harshness of it ! <g>

But in the absence of feedback from the OP, just thought to add-on a little
clarification, and another interp angle to the orig. post.
 
Top