A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How can I paste a unique values list?



 
 
Thread Tools Display Modes
  #1  
Old June 12th 05, 08:25 PM
jak roodi
external usenet poster
 
Posts: n/a
Default How can I paste a unique values list?

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
Ads
  #3  
Old June 12th 05, 09:03 PM
Joham Shason via OfficeKB.com
external usenet poster
 
Posts: n/a
Default

Hi,

You can do it by using the filter options in data menu. Just check this
steps out.

Go to data --> FILTER --> advanced filter --> Select copy to another
location --> select the list range -> select the new location where data
should be copied --> Check Unique records and click on OK.

That should work.

C Ya.

--
Message posted via http://www.officekb.com
  #5  
Old May 24th 08, 06:57 AM posted to microsoft.public.excel.misc
unique records
external usenet poster
 
Posts: 1
Default How can I paste a unique values list?



"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

  #6  
Old May 24th 08, 07:43 PM posted to microsoft.public.excel.misc
RagDyeR
external usenet poster
 
Posts: 3,574
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


  #7  
Old May 25th 08, 01:14 PM posted to microsoft.public.excel.misc
Dorothy[_3_]
external usenet poster
 
Posts: 3
Default How can I paste a unique values list?

On 24 ΝΑΚ, 09:57, unique records <unique
> wrote:
> "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


Hi, JAK
You can use an Advanced Filter to extract a list of unique items in
your worksheet
Select a cell in the list
From the Data menu, choose Filter, Advanced Filter.
Choose 'Copy to another location'.
For the List range, select the column(s) from which you want to
extract the unique values.
Leave the Criteria Range blank.
Select a starting cell for the Copy to location.
Add a check mark to the Unique records only box.
Click OK. That's it!

If you want to find unique values between two Excel files then you
should use filord utility from www.filord.com
  #8  
Old August 26th 08, 04:26 PM posted to microsoft.public.excel.misc
K
external usenet poster
 
Posts: 108
Default How can I paste a unique values list?

Peo;

I have a similar scenario. Also a long list, several repeated values, I want
to filter unique records. However, I have 4 columns of data! I want to filter
unique records based on column A, but I want to keep corresponding values
from all 4 columns together. For example:

A B C D
228-45615-91 LUBRICANT, SHMDZ, Grease 25gms.
228-45704-91 Y Y VALVE, SHMDZ, Inlet Check Valve
228-45705-91 Y Y VALVE, SHMDZ, Outlet Check Valve
228-45707-91 Y Y FILTER, SHMDZ, SUCTION FILTER SS
228-45707-91 FILTER, SHMDZ, FOR SOLVENT
228-45708-91 Y Y FILTER, SHMDZ, SUCTION FILTER

I've tried the same method under data>filter>advance filter but I'm having
problems. Your thoughts? Is the a criteria range value i can input to do this?

thanks



"Peo Sjoblom" wrote:

> You can add a header, select the range, do data>filter>advanced filter,
> select copy to another location and unique records only
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "jak roodi" <jak > wrote in message
> ...
> >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

>
>

  #9  
Old August 26th 08, 04:53 PM posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
 
Posts: 10,124
Default How can I paste a unique values list?

Sub makeuniquelist()
Application.ScreenUpdating = False
mc = "a"
lr = Cells(Rows.Count, mc).End(xlUp).Row
With Range("A1:A" & lr)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Copy Range("F1")
Application.CutCopyMode = False
ActiveSheet.ShowAllData
End With

'don't need the rest but you may want to look at it
flr = Cells(Rows.Count, "f").End(xlUp).Row
For Each c In Range("f2:f" & flr)
With Sheets("sheet9")
dlr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Range("A1:a" & lr).AutoFilter Field:=1, Criteria1:=c
'MsgBox c
Range("A2:a" & lr).Copy .Cells(dlr, "a")
Range("A1:a" & lr).AutoFilter
End With
Next c
Range("f1:f" & flr).ClearContents
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" > wrote in message
...
> Peo;
>
> I have a similar scenario. Also a long list, several repeated values, I
> want
> to filter unique records. However, I have 4 columns of data! I want to
> filter
> unique records based on column A, but I want to keep corresponding values
> from all 4 columns together. For example:
>
> A B C D
> 228-45615-91 LUBRICANT, SHMDZ, Grease 25gms.
> 228-45704-91 Y Y VALVE, SHMDZ, Inlet Check Valve
> 228-45705-91 Y Y VALVE, SHMDZ, Outlet Check Valve
> 228-45707-91 Y Y FILTER, SHMDZ, SUCTION FILTER SS
> 228-45707-91 FILTER, SHMDZ, FOR SOLVENT
> 228-45708-91 Y Y FILTER, SHMDZ, SUCTION FILTER
>
> I've tried the same method under data>filter>advance filter but I'm having
> problems. Your thoughts? Is the a criteria range value i can input to do
> this?
>
> thanks
>
>
>
> "Peo Sjoblom" wrote:
>
>> You can add a header, select the range, do data>filter>advanced filter,
>> select copy to another location and unique records only
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> (No private emails please)
>>
>>
>> "jak roodi" <jak
> wrote in message
>> ...
>> >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

>>
>>


  #10  
Old August 26th 08, 06:57 PM posted to microsoft.public.excel.misc
K
external usenet poster
 
Posts: 108
Default How can I paste a unique values list?

Don:

Thanks for the quick reply. I'm not sure what your response means, is it a
macro or a sub-routine. I'm not fluent with either, could you brak it down to
laymans terms a little more?

thanks

K

"Don Guillett" wrote:

> Sub makeuniquelist()
> Application.ScreenUpdating = False
> mc = "a"
> lr = Cells(Rows.Count, mc).End(xlUp).Row
> With Range("A1:A" & lr)
> .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
> .Copy Range("F1")
> Application.CutCopyMode = False
> ActiveSheet.ShowAllData
> End With
>
> 'don't need the rest but you may want to look at it
> flr = Cells(Rows.Count, "f").End(xlUp).Row
> For Each c In Range("f2:f" & flr)
> With Sheets("sheet9")
> dlr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
> Range("A1:a" & lr).AutoFilter Field:=1, Criteria1:=c
> 'MsgBox c
> Range("A2:a" & lr).Copy .Cells(dlr, "a")
> Range("A1:a" & lr).AutoFilter
> End With
> Next c
> Range("f1:f" & flr).ClearContents
> Application.ScreenUpdating = True
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
>
> "K" > wrote in message
> ...
> > Peo;
> >
> > I have a similar scenario. Also a long list, several repeated values, I
> > want
> > to filter unique records. However, I have 4 columns of data! I want to
> > filter
> > unique records based on column A, but I want to keep corresponding values
> > from all 4 columns together. For example:
> >
> > A B C D
> > 228-45615-91 LUBRICANT, SHMDZ, Grease 25gms.
> > 228-45704-91 Y Y VALVE, SHMDZ, Inlet Check Valve
> > 228-45705-91 Y Y VALVE, SHMDZ, Outlet Check Valve
> > 228-45707-91 Y Y FILTER, SHMDZ, SUCTION FILTER SS
> > 228-45707-91 FILTER, SHMDZ, FOR SOLVENT
> > 228-45708-91 Y Y FILTER, SHMDZ, SUCTION FILTER
> >
> > I've tried the same method under data>filter>advance filter but I'm having
> > problems. Your thoughts? Is the a criteria range value i can input to do
> > this?
> >
> > thanks
> >
> >
> >
> > "Peo Sjoblom" wrote:
> >
> >> You can add a header, select the range, do data>filter>advanced filter,
> >> select copy to another location and unique records only
> >>
> >> --
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> (No private emails please)
> >>
> >>
> >> "jak roodi" <jak
> wrote in message
> >> ...
> >> >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
> >>
> >>

>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Keyboard Shortcut for "Paste Values"? Sam Excel Discussion (Misc queries) 6 December 23rd 09 05:17 PM
Paste values GDC Excel Worksheet Functions 0 June 9th 05 05:18 PM
paste special values jenn Excel Worksheet Functions 2 February 3rd 05 01:45 AM
Pull unique names for drop down list [email protected] Excel Discussion (Misc queries) 3 February 1st 05 10:23 PM
Count Unique Names in list w/ Additional Criteria? Nodak Excel Worksheet Functions 1 January 25th 05 11:15 PM


All times are GMT +1. The time now is 12:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2004-2014 ExcelBanter.
The comments are property of their posters.