List cell content without duplication or blanks
 

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

List cell content without duplication or blanks
 

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

"Mik" wrote:

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

List cell content without duplication or blanks
 

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


"Mik" wrote:

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

List cell content without duplication or blanks
 

On 15 Oct, 16:01, Billy Liddel
wrote:
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



"Mik" wrote:
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- Hide quoted text -

- 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.

List cell content without duplication or blanks
 

On 15 Oct, 16:30, Mik wrote:
On 15 Oct, 16:01, Billy Liddel
wrote:





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

"Mik" wrote:
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- Hide quoted text -

- 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.- Hide quoted text -

- 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

List cell content without duplication or blanks
 

=IF(ISERR(SMALL(IF((Names<"")*(MATCH(Names&"",Nam es&"",0))=ROW(INDIRECT("1:"&ROWS(Names))),MATCH(Na mes&"",Names&"",0)),ROWS($1:1))),"",INDEX(Names,SM ALL(IF((Names<"")*(MATCH(Names&"",Names&"",0))=RO W(INDIRECT("1:"&ROWS(Names))),MATCH(Names&"",Names &"",0)),ROWS($1:1))))

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


"Mik" wrote:

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

List cell content without duplication or blanks
 

On 15 Oct, 17:33, Teethless mama
wrote:
=IF(ISERR(SMALL(IF((Names<"")*(MATCH(Names&"",Nam es&"",0))=ROW(INDIRECT("1*:"&ROWS(Names))),MATCH(N ames&"",Names&"",0)),ROWS($1:1))),"",INDEX(Names,S M*ALL(IF((Names<"")*(MATCH(Names&"",Names&"",0))= ROW(INDIRECT("1:"&ROWS(Name*s))),MATCH(Names&"",Na mes&"",0)),ROWS($1:1))))

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



"Mik" wrote:
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- Hide quoted text -

- Show quoted text -



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

List cell content without duplication or blanks
 

On 16 Oct, 10:30, Mik wrote:
On 15 Oct, 17:33, Teethless mama





wrote:
=IF(ISERR(SMALL(IF((Names<"")*(MATCH(Names&"",Nam es&"",0))=ROW(INDIRECT("1**:"&ROWS(Names))),MATCH( Names&"",Names&"",0)),ROWS($1:1))),"",INDEX(Names, S*M*ALL(IF((Names<"")*(MATCH(Names&"",Names&"",0) )=ROW(INDIRECT("1:"&ROWS(Na*me*s))),MATCH(Names&"" ,Names&"",0)),ROWS($1:1))))

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

"Mik" wrote:
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- Hide quoted text -

- Show quoted text -

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

- 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