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,

To get a copy of your list with only unique values, you can use the "Remove Duplicates" feature in Excel. Here are the steps:

1. Select the column that contains your list of values.
2. Click on the "Data" tab in the ribbon at the top of the Excel window.
3. Click on the "Remove Duplicates" button in the "Data Tools" group.
4. In the "Remove Duplicates" dialog box, make sure that the checkbox next to the column containing your values is checked.
5. Click the "OK" button.

Excel will then remove any duplicate values from your list and give you a new list with only unique values.

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

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

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

"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

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

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

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

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

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

This is a macro sub routine that will look in column a and extract all
unique items to column F.

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
Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
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

On the same Note (Unique Values).....
I have two columns (A1:A88) and (B1:B90)....the columns have some similar
data but I would like to filter out the data that is not unique to either of
the columns. In other words...my results should only give me data that is not
similar in both records. A majority of the data is similar but I want to
filter out data that does not match on both columns.....ANYONE???

"Dorothy" wrote:

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

Kagzz-
What is the relationship between the columns?
Sight unseen, one thought would be to use column C to concatenate AB, then
dedup that. That idea presupposes a couple things, though: that neither A
nor B values are completely unique, and that the 'hit' ratio between the two
would produce a useful result.
Example:
Alpha Beta AlphaBeta
Gamma Delta GammaDelta
Alpha Delta AlphaDelta
Gamma Beta GammaBeta

In the third column, you now have a single value representing the pair which
you can manipulate/deduplicate/autofilter for unique.

"Kagzz" wrote:

On the same Note (Unique Values).....
I have two columns (A1:A88) and (B1:B90)....the columns have some similar
data but I would like to filter out the data that is not unique to either of
the columns. In other words...my results should only give me data that is not
similar in both records. A majority of the data is similar but I want to
filter out data that does not match on both columns.....ANYONE???

Hi,
I am following the steps below which are programmed as part of a simple
macro, but the unique value in the first line of the filtered results is
being repeated as the in the last line of the filtered results. I have
checked spacing of the data, re created the macro multiple times and cleaned
up the source data. I can't figure out why that one value is being repeated.
Any advice is appreciated.
Thanks
Tom

"Joham Shason via OfficeKB.com" wrote:

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