Hi all,

I have a need to select and store unique cell contents in a large number of
rows. I guess I would like a formula that looks something like
=unique(a2:a22,","," ") where the information I would to test is in cells a2
through a22, seperate the contents of the unique cell contents, and ignore
cells containing " ".

supl1 supl2 supl3 supl4 supl5 Unique
order1 aa aa ab cd aa,ab,ac
order2 aa aa aa aa
order3 cd ab ef dd cd,ab,ef,dd

And so on. I expect something like this could be built using VB, but I am
VB challenged. I wonder if there is already such a function or where I might
go to get some help building one?

As always, thanks so much for your help.
--
Bill Wehrmacher

You can use filteradvanced filter, copy to another location and select
unique records only.
If you have Excel 2007 it has a built in functionality called remove
duplicates


--


Regards,


Peo Sjoblom



"Wehrmacher" wrote in message
...
Hi all,

I have a need to select and store unique cell contents in a large number
of
rows. I guess I would like a formula that looks something like
=unique(a2:a22,","," ") where the information I would to test is in cells
a2
through a22, seperate the contents of the unique cell contents, and ignore
cells containing " ".

supl1 supl2 supl3 supl4 supl5 Unique
order1 aa aa ab cd aa,ab,ac
order2 aa aa aa aa
order3 cd ab ef dd cd,ab,ef,dd

And so on. I expect something like this could be built using VB, but I am
VB challenged. I wonder if there is already such a function or where I
might
go to get some help building one?

As always, thanks so much for your help.
--
Bill Wehrmacher

Thanks Peo,

I looked at the advanced filter before writing and couldn't make it do what
I wanted. I will look again. I think the Excel has the nearly same
features, but I don't recall specifically seeing the remove duplicates...

Thanks again
--
Bill Wehrmacher


"Peo Sjoblom" wrote:

You can use filteradvanced filter, copy to another location and select
unique records only.
If you have Excel 2007 it has a built in functionality called remove
duplicates


--


Regards,


Peo Sjoblom



"Wehrmacher" wrote in message
...
Hi all,

I have a need to select and store unique cell contents in a large number
of
rows. I guess I would like a formula that looks something like
=unique(a2:a22,","," ") where the information I would to test is in cells
a2
through a22, seperate the contents of the unique cell contents, and ignore
cells containing " ".

supl1 supl2 supl3 supl4 supl5 Unique
order1 aa aa ab cd aa,ab,ac
order2 aa aa aa aa
order3 cd ab ef dd cd,ab,ef,dd

And so on. I expect something like this could be built using VB, but I am
VB challenged. I wonder if there is already such a function or where I
might
go to get some help building one?

As always, thanks so much for your help.
--
Bill Wehrmacher

Hi again,

I looked at the advanced filter. It sort of does what I need with a couple
of shortcomings. The first is that it only seems to work to filter columns.
I can deal with that by transposing my spreadsheet, although Excel's 256
columns are insufficient. The second is that I can't find a way to make the
advanced filter filter more than one column at a time. As I have several
thousand rows to analyse, this will be a difficult approach.

Thanks.
--
Bill Wehrmacher


"Wehrmacher" wrote:

Thanks Peo,

I looked at the advanced filter before writing and couldn't make it do what
I wanted. I will look again. I think the Excel has the nearly same
features, but I don't recall specifically seeing the remove duplicates...

Thanks again
--
Bill Wehrmacher


"Peo Sjoblom" wrote:

You can use filteradvanced filter, copy to another location and select
unique records only.
If you have Excel 2007 it has a built in functionality called remove
duplicates


--


Regards,


Peo Sjoblom



"Wehrmacher" wrote in message
...
Hi all,

I have a need to select and store unique cell contents in a large number
of
rows. I guess I would like a formula that looks something like
=unique(a2:a22,","," ") where the information I would to test is in cells
a2
through a22, seperate the contents of the unique cell contents, and ignore
cells containing " ".

supl1 supl2 supl3 supl4 supl5 Unique
order1 aa aa ab cd aa,ab,ac
order2 aa aa aa aa
order3 cd ab ef dd cd,ab,ef,dd

And so on. I expect something like this could be built using VB, but I am
VB challenged. I wonder if there is already such a function or where I
might
go to get some help building one?

As always, thanks so much for your help.
--
Bill Wehrmacher

Bill,

Copy the UDF code below into a regular codemodule in your project, then use it like

=Unique(A2:H2)

and copy it down...

HTH,
Bernie
MS Excel MVP

Function Unique(inRange As Range) As String
Dim myC As Range
Unique = ""
For Each myC In inRange
If InStr(1, Unique, " " & myC.Value & ",") = 0 Then
Unique = Unique & " " & myC.Value & ","
End If
Next myC
Unique = Mid(Unique, 2, Len(Unique) - 2)
End Function


"Wehrmacher" wrote in message
...
Hi all,

I have a need to select and store unique cell contents in a large number of
rows. I guess I would like a formula that looks something like
=unique(a2:a22,","," ") where the information I would to test is in cells a2
through a22, seperate the contents of the unique cell contents, and ignore
cells containing " ".

supl1 supl2 supl3 supl4 supl5 Unique
order1 aa aa ab cd aa,ab,ac
order2 aa aa aa aa
order3 cd ab ef dd cd,ab,ef,dd

And so on. I expect something like this could be built using VB, but I am
VB challenged. I wonder if there is already such a function or where I might
go to get some help building one?

As always, thanks so much for your help.
--
Bill Wehrmacher

Thanks Bernie,

You certainly are an MVP. The code works very well.

I really appreciate it, and so will my boss. ;-)
--
Bill Wehrmacher


"Bernie Deitrick" wrote:

Bill,

Copy the UDF code below into a regular codemodule in your project, then use it like

=Unique(A2:H2)

and copy it down...

HTH,
Bernie
MS Excel MVP

Function Unique(inRange As Range) As String
Dim myC As Range
Unique = ""
For Each myC In inRange
If InStr(1, Unique, " " & myC.Value & ",") = 0 Then
Unique = Unique & " " & myC.Value & ","
End If
Next myC
Unique = Mid(Unique, 2, Len(Unique) - 2)
End Function


"Wehrmacher" wrote in message
...
Hi all,

I have a need to select and store unique cell contents in a large number of
rows. I guess I would like a formula that looks something like
=unique(a2:a22,","," ") where the information I would to test is in cells a2
through a22, seperate the contents of the unique cell contents, and ignore
cells containing " ".

supl1 supl2 supl3 supl4 supl5 Unique
order1 aa aa ab cd aa,ab,ac
order2 aa aa aa aa
order3 cd ab ef dd cd,ab,ef,dd

And so on. I expect something like this could be built using VB, but I am
VB challenged. I wonder if there is already such a function or where I might
go to get some help building one?

As always, thanks so much for your help.
--
Bill Wehrmacher