View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
RagDyeR RagDyeR is offline
external usenet poster
 
Posts: 3,572
Default How can I paste a unique values list?

Say your list of values was in A1 to A1000.
In B1 enter
=A1
And in B2 enter this *array* formula:

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$1000&"" ),0)),"",INDEX(IF(ISBLANK(
$A$1:$A$1000),"",$A$1:$A$1000),MATCH(0,COUNTIF(B$1 :B1,$A$1:$A$1000&""),0)))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy down until you get blank returns.

If your list is very large, this will slow you down somewhat!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"unique records" <unique wrote in message
...


"jak roodi" wrote:

I have a very big list contains a lot of repeated values and I want to

get a
copy of the same list BUT only with unique values. For Example the

column
Reg.No. includes the following Numbers:
1154
1168
1166
1154
908455
907558
1166
908455
1154
1166

List might have thousans of records. I want to get a copy of this list
without repeating any of the numbers included. { Uniqe value list }.

Thanks and regards.

JAK