List cell content without duplication or blanks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mik - 15 Oct 2007 15:33 GMT

Hello, looking for some help with the following:-
I have a list of names within Column A, some of which are repeated
numerous times.
What i require, is to list in Column B each of the names shown in
Column A (once only), so those names that are repeated, are only
displayed once.

Col A Col B

A A
B B
C C
B D
D E
E F
A
A
F

Reply to this Message

Billy Liddel - 15 Oct 2007 16:01 GMT

Max showed this the other day

In c1 type; =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW()))
in d1 type; =IF(ROW()>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW())))

drag both formulas down as far as required C to end of data and d until no
further results are shown.

Peter

> Hello, looking for some help with the following:-
> I have a list of names within Column A, some of which are repeated
[quoted text clipped - 14 lines]
> A
> F

Reply to this Message

Mik - 15 Oct 2007 16:30 GMT

On 15 Oct, 16:01, Billy Liddel <BillyLid...@discussions.microsoft.com>
wrote:

> Max showed this the other day
>
[quoted text clipped - 26 lines]
>
> - Show quoted text -

Peter,

Thanks for your answer.

If i explain the purpose of my subject (which i should have done in my
first post), may'be you could help me further, or show an easier way
of achieving this.

As mentioned, i have Column A showing many names (some are
duplicated).
I want to create a list box containing these names.

So, i was trying to show the conents of column A in Column B with the
repeated names and blank cells removed.

I would then apply Data validation to the range of cells in column B.

Your answer, taught me something, but i'm not sure if it works in such
a way that i require.

Any thoughts?

Thanks Mike.

Reply to this Message

Mik - 15 Oct 2007 16:46 GMT

> On 15 Oct, 16:01, Billy Liddel <BillyLid...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 55 lines]
>
> - Show quoted text -

Thanks for your input Soundar,

Not sure if my last reply sent correctly, so here it is again.

Can this Auto Filter be applied (possibly in formula type) so as
column A expands with new data, column B automatically refreshes to
show the filtered data?

Mike

Reply to this Message

Soundar - 15 Oct 2007 16:21 GMT

Hi,

You can solve this problem by using Advanced Filter option in Excel.

For example

A (Column)

Name
a
c
a
e
a
d
a
e

Just click on the label (in the above example it is Name (Cell A1), And then
click

Data--> Advanced Filter --> And then click the check box, Unique records
only check box. And also give the cell address in which you want to display
the unique records (Copy to option in dialog box, example B1).

Then you will get the following output

A B
name    name
a    a
c    c
a    e
e    d
a
d
a
e

Hope this will clear your doubt.

Regards,
Soundar

> Hello, looking for some help with the following:-
> I have a list of names within Column A, some of which are repeated
[quoted text clipped - 14 lines]
> A
> F

Reply to this Message

Teethless mama - 15 Oct 2007 17:33 GMT

=IF(ISERR(SMALL(IF((Names<>"")*(MATCH(Names&"",Names&"",0))=ROW(INDIRECT("1:"&ROWS(Names))),MATCH(Names&"",Names&"",0)),ROWS($1:1))),"",INDEX(Names,SMALL(IF((Names<>"")*(MATCH(Names&"",Names&"",0))=ROW(INDIRECT("1:"&ROWS(Names))),MATCH(Names&"",Names&"",0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

> Hello, looking for some help with the following:-
> I have a list of names within Column A, some of which are repeated
[quoted text clipped - 14 lines]
> A
> F

Reply to this Message

Mik - 16 Oct 2007 10:30 GMT

On 15 Oct, 17:33, Teethless mama
<Teethlessm...@discussions.microsoft.com> wrote:

> =IF(ISERR(SMALL(IF((Names<>"")*(MATCH(Names&"",Names&"",0))=ROW(INDIRECT("1?:"&ROWS(Names))),MATCH(Names&"",Names&"",0)),ROWS($1:1))),"",INDEX(Names,SM?ALL(IF((Names<>"")*(MATCH(Names&"",Names&"",0))=ROW(INDIRECT("1:"&ROWS(Name?s))),MATCH(Names&"",Names&"",0)),ROWS($1:1))))
>
[quoted text clipped - 21 lines]
>
> - Show quoted text -

I'm not having too much joy with this one... can't get it to work.

Reply to this Message

Mik - 16 Oct 2007 10:53 GMT

> On 15 Oct, 17:33, Teethless mama
>
[quoted text clipped - 28 lines]
>
> - Show quoted text -

Thanks for EVERYONE'S help.
Done what i wanted using following code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A1:A50").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("B1:B50"), Unique:=True

Range("B2:B50").Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

End Sub

Reply to this Message