Unique cells in a row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wehrmacher - 01 May 2008 15:42 GMT

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.

Signature

Bill Wehrmacher

Reply to this Message

Peo Sjoblom - 01 May 2008 15:58 GMT

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

Signature

Regards,

Peo Sjoblom

> Hi all,
>
[quoted text clipped - 17 lines]
>
> As always, thanks so much for your help.

Reply to this Message

Wehrmacher - 01 May 2008 16:26 GMT

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

Signature

Bill Wehrmacher

> You can use filter>advanced filter, copy to another location and select
> unique records only.
[quoted text clipped - 22 lines]
> >
> > As always, thanks so much for your help.

Reply to this Message

Wehrmacher - 01 May 2008 17:24 GMT

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.

Signature

Bill Wehrmacher

> Thanks Peo,
>
[quoted text clipped - 30 lines]
> > >
> > > As always, thanks so much for your help.

Reply to this Message

Bernie Deitrick - 01 May 2008 17:39 GMT

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

> Hi all,
>
[quoted text clipped - 14 lines]
>
> As always, thanks so much for your help.

Reply to this Message

Wehrmacher - 01 May 2008 20:05 GMT

Thanks Bernie,

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

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

Signature

Bill Wehrmacher

> Bill,
>
[quoted text clipped - 37 lines]
> >
> > As always, thanks so much for your help.

Reply to this Message