Unique Cells

[Abnerz]

Hi, hope you can help.
Column 1 Column 2
4 Al
5 John
5 Pete
4 Rob
5 Al
4 Paul
5 Rob

Answer to above:
For 4:
Al
Rob
Paul



Based on colmn E, I want to list the UNIQUE names in column 2

 

[Abnerz]

I meant to say, based on column 1, I want to list the UNIQUE names in column 2

 

[JBeaucaire]

Without adding another column of "helper data", you'll need to use a pretty
hefty array formula for a simple task. As long as the data set isn't huge,
this should work.

With numbers in column A, names in Column B, enter this formula in ROW1
somewhere. It needs to be row1, like C1, or D1, or H1....

=IF(ROW()>COUNTIF($A$1:$A$7,4),"",INDEX($B$1:$B$7,
SMALL(IF($A$1:$A$7=4,ROW($A$1:$A$7),999),ROW())))

....and press CTRL-SHIFT-ENTER to confirm the formula array. Just pressing
ENTER will break the array and you will only get the first answer. So,
CTRL-SHIFT-ENTER and braces { } will appear around your formula.

Now copy that cell down and the names will appear.

You can expand it, but expand all the ranges equally.

 

[JB]

Code in E2

Named ranges:
Codes $A$2:$A$19
Names $B$2:$B$19

-Select E5
=IF(ROWS($E$1:$E1)<=COUNTIF(Codes,$E$2),
INDEX(Names,SMALL(IF(Codes=$E$2,ROW(INDIRECT("1:"&ROWS(Codes)))),ROWS
($E$1:$E1))),"")
-Valid with Shift+Ctrl+Enter

http://cjoint.com/?dDiqs3IvhW

You may move named range or formula whitout modify formula.

JB
http://boisgontierjacques.free.fr

 

[Abnerz]

Thanks JB. Your link to the example realy helped. The only thing is that it
does not give a unique answer. In your example, if you add in row 10, 4 and
Al, column E will show Al 2 times.
What you did is a huge help to me anyway. Just curious if there is another
step to add to formula??

Thanks Agaian!!

 

[JB]

http://cjoint.com/?dDppcu0GGw

=IF(ROWS($1:1)<=COUNT(1/FREQUENCY(IF(Codes=$E$2,MATCH(Names,Names,
0)),IF(Codes=$E$2,MATCH(Names,Names,0)))),
INDEX(Names,MIN(IF(Names<>"",IF((COUNTIF(E$4:E4,Names)=0)*(Codes=E
$2),ROW(INDIRECT("1:"&ROWS(Names))))))),"")

JB

 

[Abnerz]

That's it!!! Thanks you for all your help!!!!!!!!!!