Create Unique List Of 5 Letter Words From A List

[scampitelli]

My problem is that i have a column of (1 to 6) letter words.
I would like to create a unique list showing all 5 letter words in that list sorted or unsorted.
I would like this done by using an excel formula only.
Is this possible?

Thanks in advance for anybodys help.

 

[scampitelli]

Thank you, Tony and PGC.
You two were so helpful.
Tony, I am still trying to understand your answer - but I am sure it works.
PGC - that is a pretty slick formula, I like how you handled the UNIQUE side of my problem.
Thanks again.

 

[pgc01]

You're welcome. Thanks for the feedback.

I am using 2007.

I came up with this to put in cell c2 - then copy down to c1001:

=IFERROR(INDEX($A$2:$A$1001,SMALL(IF(LEN($A$2:$A$1001)=5,ROW($A$2:$A$1001)-ROW(A$2)+1),ROWS(A$2:A2))),"")

Must use <CONTROL><SHIFT><ENTER>- array formula.

BUT - it is not a unique list.

Remark: If you want to use the Index(,Small()) construct, adding the condition for uniqueness, another option, in B2 as array formula:

=IFERROR(INDEX($A$2:$A$13,SMALL(IF(LEN($A$2:$A$13)=5,IF(COUNTIF(OFFSET($A$2,0,0,ROW($A$2:$A$13)-ROW($A$2)+1,1),$A$2:$A$13)=1,ROW($A$2:$A$13)-ROW($A$2)+1)),ROWS($B$2:B2))),"")

In this case the items in the output appear in the same order as in the input list. Not as efficient as the other formula, though.