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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
You can add a header, select the range, do datafilteradvanced 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Jak
If you want the unique list to be in another sheet be sure to activate that sheet prior to using Peo's and Joham's suggestion. -- Best Regards Leo Heuser Followup to newsgroup only please. "jak roodi" jak skrev i en meddelelse ... 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 |
#5
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 datafilteradvance 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 datafilteradvanced 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
|
|||
|
|||
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 datafilteradvance 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 datafilteradvanced 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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
URGENT HELP: Pick out unique values from repeated values in one ce | Sonal | Worksheet Functions | 4 | September 13th, 2004 06:30 PM |
Formula to return non-zero values in a list | Naoki | General Discussion | 4 | August 17th, 2004 01:51 PM |
How do you Count the number of unique values in a list ? | owenchan | Worksheet Functions | 1 | June 3rd, 2004 04:43 PM |
LIST unique values | James Gilbert | Worksheet Functions | 3 | May 31st, 2004 11:16 AM |
Unique entries in a filtered list | Worksheet Functions | 1 | November 21st, 2003 01:08 PM |