|
|
|
|
|
|
|
|
|
|
| |
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 roodi@discussions.microsoft.com> wrote in message
> news:E37FD47E-2A6B-43BF-9A41-BA45425E9978@microsoft.com...
> >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
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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
dguillett1@austin.rr.com
"K" <K@discussions.microsoft.com> wrote in message
news:905EEAA9-EDD2-459F-A4D8-AAC07FD77E87@microsoft.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 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 roodi@discussions.microsoft.com> wrote in message
>> news:E37FD47E-2A6B-43BF-9A41-BA45425E9978@microsoft.com...
>> >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
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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
> dguillett1@austin.rr.com
> "K" <K@discussions.microsoft.com> wrote in message
> news:905EEAA9-EDD2-459F-A4D8-AAC07FD77E87@microsoft.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 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 roodi@discussions.microsoft.com> wrote in message
> >> news:E37FD47E-2A6B-43BF-9A41-BA45425E9978@microsoft.com...
> >> >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
> >>
> >>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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
dguillett1@austin.rr.com
"K" <K@discussions.microsoft.com> wrote in message
news:10B6213A-CBC4-41BA-A727-C305EEDB988B@microsoft.com...
> 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
>> dguillett1@austin.rr.com
>> "K" <K@discussions.microsoft.com> wrote in message
>> news:905EEAA9-EDD2-459F-A4D8-AAC07FD77E87@microsoft.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 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 roodi@discussions.microsoft.com> wrote in message
>> >> news:E37FD47E-2A6B-43BF-9A41-BA45425E9978@microsoft.com...
>> >> >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
>> >>
>> >>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
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 records@discussions.microsoft.com> wrote in message
news:83B1C7DA-27D9-45AD-A14E-79E9E9F755B8@microsoft.com...
>
>
> "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
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Question
Answer
Notify me of replies