List cell content without duplication or blanks

  • Thread starter Thread starter Mik
  • Start date Start date
M

Mik

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







- Show quoted text -
Click to expand...


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.
 
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 -
Click to expand...


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
 
=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
 
=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))))

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







- Show quoted text -
Click to expand...



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

- Show quoted text -
Click to expand...



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
 
Back
Top