Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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. #1
    New Member
    Join Date
    Nov 2011
    Posts
    5

    Default 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. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    2,786

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

    AbUsing Excel 2002, 2007
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    Board Regular sitewolf's Avatar
    Join Date
    May 2012
    Location
    South Dakota
    Posts
    213

    Default 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. #4
    New Member
    Join Date
    Nov 2011
    Posts
    5

    Default 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. #5
    Board Regular sitewolf's Avatar
    Join Date
    May 2012
    Location
    South Dakota
    Posts
    213

    Default 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. #6
    New Member
    Join Date
    Nov 2011
    Posts
    5

    Default 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. #7
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    2,786

    Default Re: Create Unique List Of 5 Letter Words From A List

    What version of Excel are you using?
    Tony

    AbUsing Excel 2002, 2007
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  8. #8
    New Member
    Join Date
    Nov 2011
    Posts
    5

    Default 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. #9
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    2,786

    Default Re: Create Unique List Of 5 Letter Words From A List

    scampitelli

    Sorry for delay in further response.
    Does this help?

    Sheet1

    AB
    1Source ListUnique List
    2ttRRRRR
    3RRRRRBBBVV
    4RRRRRWWWWW
    5BBBVVEEEEE
    6WWWWWXCFDR
    7EEEEERTYUI
    8ss
    9EEEEE
    10RRRRR
    11XCFDR
    12XXXS
    13RTYUI
    14
    15
    16

    Spreadsheet Formulas
    CellFormula
    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
    Tony

    AbUsing Excel 2002, 2007
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,266

    Default Re: Create Unique List Of 5 Letter Words From A List

    Quote Originally Posted by scampitelli View Post
    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
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com