# Create Unique List Of 5 Letter Words From A List

This is a discussion on Create Unique List Of 5 Letter Words From A List within the Excel Questions forums, part of the Question Forums category; My problem is that i have a column of (1 to 6) letter words. I would like to create a ...

1. ## Create Unique List Of 5 Letter Words From A List

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.

2. ## Re: Create Unique List Of 5 Letter Words From A List

scampitelli

You could perhaps employ a 'helper' column that holds the length of your words and then filter the data on the value 5 in the helper column.

Eg Col A = your words. Col B has formula..

In B1....... =LEN(A1) etc.

Hope that helps.

3. ## Re: Create Unique List Of 5 Letter Words From A List

well, =len(A1) (where A is the data column) would return the number of characters and a filter could filter out <>5?

....or what Snakehips just said :p

4. ## Re: Create Unique List Of 5 Letter Words From A List

Thank you for your reply - that would be the best method, I agree, but the solution requires an excel formula.

I attempted to use INDEX and returning the rows that have words of length 5 but somehow not quite getting it.

5. ## Re: Create Unique List Of 5 Letter Words From A List

not sure I'm following the why here, but...

=if(len(A1)=5,A1,"") will spit only 5 letter words into that column

6. ## Re: Create Unique List Of 5 Letter Words From A List

Hi Tony,

By your responses I am not explaining myself properly.

The solution to this question would be to produce an excel formula in cell c2 that can be copied down to c1001. The excel list of words are in cells a2:a1001. In cells c2:c1001 will contain a unique list of words from column a that are of length 5. This list in column c could be considered dynamic since it will change as your column a changes.

Data filtering or pivot tables is not an option here.

I hope I explained myself clearly now.

Thanks for your ongoing help.

7. ## Re: Create Unique List Of 5 Letter Words From A List

What version of Excel are you using?

8. ## Re: Create Unique List Of 5 Letter Words From A List

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

BUT - it is not a unique list.

9. ## Re: Create Unique List Of 5 Letter Words From A List

scampitelli

Sorry for delay in further response.
Does this help?

Sheet1

 A B 1 Source List Unique List 2 tt RRRRR 3 RRRRR BBBVV 4 RRRRR WWWWW 5 BBBVV EEEEE 6 WWWWW XCFDR 7 EEEEE RTYUI 8 ss 9 EEEEE 10 RRRRR 11 XCFDR 12 XXXS 13 RTYUI 14 15 16

Spreadsheet Formulas
 Cell Formula B2 {=IFERROR(INDEX(INDIRECT("A\$2:A\$"&COUNTA(\$A\$2:\$A\$1001)+1),SMALL(IF((MATCH(INDIRECT("A\$2:A\$"&COUNTA(\$A\$2:\$A\$1001)+1),INDIRECT("A\$2:A\$"&COUNTA(\$A\$2:\$A\$1001)+1),0)+1=ROW(INDIRECT("A\$2:A\$"&COUNTA(\$A\$2:\$A\$1001)+1)))*(LEN(INDIRECT("A\$2:A\$"&COUNTA(\$A\$2:\$A\$1001)+1))=5)=1,ROW(INDIRECT("A\$2:A\$"&COUNTA(\$A\$2:\$A\$1001)+1))-1,10000),ROW(\$A2)-1)),"")}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

10. ## Re: Create Unique List Of 5 Letter Words From A List

Originally Posted by 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.
Hi

Try in B2:

=IFERROR(LOOKUP(2,1/(LEN(\$A\$2:\$A\$13)=5)/ISNA(MATCH(\$A\$2:\$A\$13,\$B\$1:B1,0)),\$A\$2:\$A\$13),"")

Copy down

ABC
1Source ListUnique List
2ttRTYUI
3RRRRRXCFDR
4RRRRRRRRRR
5BBBVVEEEEE
6WWWWWWWWWW
7EEEEEBBBVV
8ss
9EEEEE
10RRRRR
11XCFDR
12XXXS
13RTYUI
14
[Book2]Sheet1

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•