Make a list that excludes duplicates
 

I have a listing that looks like this:

AAA 1
AAA 2
AAA 3
BB 1
BB 2
BB 3
BB 4
C 1
DD 1
DD 2
Etc.....

I want another list to link to this but only pick up the unique text entries
excluding the numbers, so the result should be:
AAA
BB
C
DD
Etc.....

If there have to be spaces in this second list then so be it, but I would
love to find a solution.

In addition, the number at the end will always be a single digit number.
Presumably that helps as I imagine the solution will contain a
left(len(a1)-2) or something similar.....

--
http://www.redbrick.dcu.ie/~pele


"Phil Osman" wrote:

I have a listing that looks like this:

AAA 1
AAA 2
AAA 3
BB 1
BB 2
BB 3
BB 4
C 1
DD 1
DD 2
Etc.....

I want another list to link to this but only pick up the unique text entries
excluding the numbers, so the result should be:
AAA
BB
C
DD
Etc.....

If there have to be spaces in this second list then so be it, but I would
love to find a solution.

You might try the UNIQUEVALUES function in Laurent Longre's MOREFUNC.XLL
which can be downloaded from
http://xcell05.free.fr/

Highlight a range of the same size as your data and use
UNIQUEVALUES(TRIM(LEFT(data,LEN(data)-1)))
array entered (Ctrl-Shift-Enter)

Jerry


Phil Osman wrote:

I have a listing that looks like this:

AAA 1
AAA 2
AAA 3
BB 1
BB 2
BB 3
BB 4
C 1
DD 1
DD 2
Etc.....

I want another list to link to this but only pick up the unique text entries
excluding the numbers, so the result should be:
AAA
BB
C
DD
Etc.....

If there have to be spaces in this second list then so be it, but I would
love to find a solution.

No downloads allowed in work sadly......any other way that I could get the
same result ?

--
http://www.redbrick.dcu.ie/~pele


"Jerry W. Lewis" wrote:

You might try the UNIQUEVALUES function in Laurent Longre's MOREFUNC.XLL
which can be downloaded from
http://xcell05.free.fr/

Highlight a range of the same size as your data and use
UNIQUEVALUES(TRIM(LEFT(data,LEN(data)-1)))
array entered (Ctrl-Shift-Enter)

Jerry


Phil Osman wrote:

I have a listing that looks like this:

AAA 1
AAA 2
AAA 3
BB 1
BB 2
BB 3
BB 4
C 1
DD 1
DD 2
Etc.....

I want another list to link to this but only pick up the unique text entries
excluding the numbers, so the result should be:
AAA
BB
C
DD
Etc.....

If there have to be spaces in this second list then so be it, but I would
love to find a solution.

You could use a helper column to extract the first portion of the cell (assuming
that AAA 1 is in one cell).

=LEFT(A1,SEARCH(" ",A1&" ")-1)
(and copy down)

Then you could use Data|Filter|Advanced filter to get that unique list.

Debra Dalgleish has detailed instructions at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Phil Osman wrote:

I have a listing that looks like this:

AAA 1
AAA 2
AAA 3
BB 1
BB 2
BB 3
BB 4
C 1
DD 1
DD 2
Etc.....

I want another list to link to this but only pick up the unique text entries
excluding the numbers, so the result should be:
AAA
BB
C
DD
Etc.....

If there have to be spaces in this second list then so be it, but I would
love to find a solution.

--

Dave Peterson

Thanks Dave. I ended up setting the advanced filter up as a Macro, which
worked fine.

--
http://www.redbrick.dcu.ie/~pele


"Dave Peterson" wrote:

You could use a helper column to extract the first portion of the cell (assuming
that AAA 1 is in one cell).

=LEFT(A1,SEARCH(" ",A1&" ")-1)
(and copy down)

Then you could use Data|Filter|Advanced filter to get that unique list.

Debra Dalgleish has detailed instructions at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Phil Osman wrote:

I have a listing that looks like this:

AAA 1
AAA 2
AAA 3
BB 1
BB 2
BB 3
BB 4
C 1
DD 1
DD 2
Etc.....

I want another list to link to this but only pick up the unique text entries
excluding the numbers, so the result should be:
AAA
BB
C
DD
Etc.....

If there have to be spaces in this second list then so be it, but I would
love to find a solution.

--

Dave Peterson