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.
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 ...
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
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 to 2013
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
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
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.
not sure I'm following the why here, but...
=if(len(A1)=5,A1,"") will spit only 5 letter words into that column
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.
What version of Excel are you using?
Tony
AbUsing Excel 2002 to 2013
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
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.
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
Tony
AbUsing Excel 2002 to 2013
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
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
A B C 1 Source List Unique List 2 tt RTYUI 3 RRRRR XCFDR 4 RRRRR RRRRR 5 BBBVV EEEEE 6 WWWWW WWWWW 7 EEEEE BBBVV 8 ss 9 EEEEE 10 RRRRR 11 XCFDR 12 XXXS 13 RTYUI 14 [Book2]Sheet1
Kind regards
PGC
To understand recursion, you must understand recursion.
Like this thread? Share it with others