Delete duplicate numbers
 

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

Thanks

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Noemi" wrote in message
...
How do I delete duplicate numbers from a row using a formula rather then
finding each one seperately.

Thanks

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"
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"deleting duplicates" is equivalent to "extracting uniques"

Max, are you a politician by any chance?

<vbg

Biff

"Max" wrote in message
...
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"
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--