Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Similar Words In An Excel Document
Hi!
I've checked a few threads on this matter, but they don't quite cover what I'm after. I have an Excel document with several columns full of words and numbers. I have a Master column, which contains the Initial List. (A) I want to compare all the remaining columns to A, but not to each other. So that for example; B,C,D,E,F,G all compare against A and highlight within themselves, any words that match anything in A. The Slave columns will continue to grow, so the Formula would be something that can be copied and pasted to new Columns with minimal change. Preferably something that checks the entire column, with no number limit, as the columns are of varying length. Any help would be really, really appreciated, and I hope I've explained myself well enough! Thanks!! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Similar Words In An Excel Document
In column A I have a list of fruits: apple, banana, pear, plum, .......
I highlighted all of B:G (I selected the column headings of these cells), I then used Format, Conditional Formatting with Formula Is: =COUNTIF($A:$A,B1) and I formatted for a green background Note that the B1 will be changed by Excel in other columns Now when there is a word in B:G that has a match in A, the cell with that word has a green background I took you to mean a single word or phrase match. So "apple pie" in B will not go green just with "apple" somewhere in A. It would required "apple pie" in A. Tell us if is satisfactory best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Hi! I've checked a few threads on this matter, but they don't quite cover what I'm after. I have an Excel document with several columns full of words and numbers. I have a Master column, which contains the Initial List. (A) I want to compare all the remaining columns to A, but not to each other. So that for example; B,C,D,E,F,G all compare against A and highlight within themselves, any words that match anything in A. The Slave columns will continue to grow, so the Formula would be something that can be copied and pasted to new Columns with minimal change. Preferably something that checks the entire column, with no number limit, as the columns are of varying length. Any help would be really, really appreciated, and I hope I've explained myself well enough! Thanks!! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Similar Words In An Excel Document
Excellent!
Now is it possible to make it check for a partial match? So that the word 'apple sauce' for example, would come as a match if only the word 'apple' were to be listed in the A column? Thanks! "Bernard Liengme" wrote: In column A I have a list of fruits: apple, banana, pear, plum, ....... I highlighted all of B:G (I selected the column headings of these cells), I then used Format, Conditional Formatting with Formula Is: =COUNTIF($A:$A,B1) and I formatted for a green background Note that the B1 will be changed by Excel in other columns Now when there is a word in B:G that has a match in A, the cell with that word has a green background I took you to mean a single word or phrase match. So "apple pie" in B will not go green just with "apple" somewhere in A. It would required "apple pie" in A. Tell us if is satisfactory best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Hi! I've checked a few threads on this matter, but they don't quite cover what I'm after. I have an Excel document with several columns full of words and numbers. I have a Master column, which contains the Initial List. (A) I want to compare all the remaining columns to A, but not to each other. So that for example; B,C,D,E,F,G all compare against A and highlight within themselves, any words that match anything in A. The Slave columns will continue to grow, so the Formula would be something that can be copied and pasted to new Columns with minimal change. Preferably something that checks the entire column, with no number limit, as the columns are of varying length. Any help would be really, really appreciated, and I hope I've explained myself well enough! Thanks!! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Similar Words In An Excel Document
This will work if the 'slave' cell has the A-word as the first word
=COUNTIF($A:$A,IF(ISERROR(FIND(" ",B1)),B1,LEFT(B1,FIND(" ",B1)-1))) Suggest you copy from here to the format dialog So with 'apple" in A, both 'apple pie' and 'apple tart' will hit the mark but 'sour apple' will not -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Excellent! Now is it possible to make it check for a partial match? So that the word 'apple sauce' for example, would come as a match if only the word 'apple' were to be listed in the A column? Thanks! "Bernard Liengme" wrote: In column A I have a list of fruits: apple, banana, pear, plum, ....... I highlighted all of B:G (I selected the column headings of these cells), I then used Format, Conditional Formatting with Formula Is: =COUNTIF($A:$A,B1) and I formatted for a green background Note that the B1 will be changed by Excel in other columns Now when there is a word in B:G that has a match in A, the cell with that word has a green background I took you to mean a single word or phrase match. So "apple pie" in B will not go green just with "apple" somewhere in A. It would required "apple pie" in A. Tell us if is satisfactory best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Hi! I've checked a few threads on this matter, but they don't quite cover what I'm after. I have an Excel document with several columns full of words and numbers. I have a Master column, which contains the Initial List. (A) I want to compare all the remaining columns to A, but not to each other. So that for example; B,C,D,E,F,G all compare against A and highlight within themselves, any words that match anything in A. The Slave columns will continue to grow, so the Formula would be something that can be copied and pasted to new Columns with minimal change. Preferably something that checks the entire column, with no number limit, as the columns are of varying length. Any help would be really, really appreciated, and I hope I've explained myself well enough! Thanks!! |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Similar Words In An Excel Document
Excellent, that works brilliantly.
Just one last question; I've noticed that it's not finding similarities if the words are in different capitalisations. So it does not find a match between 'apple' and 'APPLE'. This is a bit of a nightmare, as one entire column is in capital letters. Is there a way to de-capitalise and turn it into standard grammar? IE: Turn 'APPLE' into 'Apple' or 'APPLE PIE' into 'Apple Pie'? Thanks, you've been a brilliant help! "Bernard Liengme" wrote: This will work if the 'slave' cell has the A-word as the first word =COUNTIF($A:$A,IF(ISERROR(FIND(" ",B1)),B1,LEFT(B1,FIND(" ",B1)-1))) Suggest you copy from here to the format dialog So with 'apple" in A, both 'apple pie' and 'apple tart' will hit the mark but 'sour apple' will not -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Excellent! Now is it possible to make it check for a partial match? So that the word 'apple sauce' for example, would come as a match if only the word 'apple' were to be listed in the A column? Thanks! "Bernard Liengme" wrote: In column A I have a list of fruits: apple, banana, pear, plum, ....... I highlighted all of B:G (I selected the column headings of these cells), I then used Format, Conditional Formatting with Formula Is: =COUNTIF($A:$A,B1) and I formatted for a green background Note that the B1 will be changed by Excel in other columns Now when there is a word in B:G that has a match in A, the cell with that word has a green background I took you to mean a single word or phrase match. So "apple pie" in B will not go green just with "apple" somewhere in A. It would required "apple pie" in A. Tell us if is satisfactory best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Hi! I've checked a few threads on this matter, but they don't quite cover what I'm after. I have an Excel document with several columns full of words and numbers. I have a Master column, which contains the Initial List. (A) I want to compare all the remaining columns to A, but not to each other. So that for example; B,C,D,E,F,G all compare against A and highlight within themselves, any words that match anything in A. The Slave columns will continue to grow, so the Formula would be something that can be copied and pasted to new Columns with minimal change. Preferably something that checks the entire column, with no number limit, as the columns are of varying length. Any help would be really, really appreciated, and I hope I've explained myself well enough! Thanks!! |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Similar Words In An Excel Document
Never mind, figured it out with =PROPER(A1) etcetc.
Thanks for your help Bernard, you've made my life a lot easier!!! |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Similar Words In An Excel Document
This is not how my worksheet behaves but if PROPER works for you then great.
Have a good week. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Excellent, that works brilliantly. Just one last question; I've noticed that it's not finding similarities if the words are in different capitalisations. So it does not find a match between 'apple' and 'APPLE'. This is a bit of a nightmare, as one entire column is in capital letters. Is there a way to de-capitalise and turn it into standard grammar? IE: Turn 'APPLE' into 'Apple' or 'APPLE PIE' into 'Apple Pie'? Thanks, you've been a brilliant help! "Bernard Liengme" wrote: This will work if the 'slave' cell has the A-word as the first word =COUNTIF($A:$A,IF(ISERROR(FIND(" ",B1)),B1,LEFT(B1,FIND(" ",B1)-1))) Suggest you copy from here to the format dialog So with 'apple" in A, both 'apple pie' and 'apple tart' will hit the mark but 'sour apple' will not -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Excellent! Now is it possible to make it check for a partial match? So that the word 'apple sauce' for example, would come as a match if only the word 'apple' were to be listed in the A column? Thanks! "Bernard Liengme" wrote: In column A I have a list of fruits: apple, banana, pear, plum, ....... I highlighted all of B:G (I selected the column headings of these cells), I then used Format, Conditional Formatting with Formula Is: =COUNTIF($A:$A,B1) and I formatted for a green background Note that the B1 will be changed by Excel in other columns Now when there is a word in B:G that has a match in A, the cell with that word has a green background I took you to mean a single word or phrase match. So "apple pie" in B will not go green just with "apple" somewhere in A. It would required "apple pie" in A. Tell us if is satisfactory best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troop" wrote in message ... Hi! I've checked a few threads on this matter, but they don't quite cover what I'm after. I have an Excel document with several columns full of words and numbers. I have a Master column, which contains the Initial List. (A) I want to compare all the remaining columns to A, but not to each other. So that for example; B,C,D,E,F,G all compare against A and highlight within themselves, any words that match anything in A. The Slave columns will continue to grow, so the Formula would be something that can be copied and pasted to new Columns with minimal change. Preferably something that checks the entire column, with no number limit, as the columns are of varying length. Any help would be really, really appreciated, and I hope I've explained myself well enough! Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spell check flagging words I can't find in Excel | Excel Discussion (Misc queries) | |||
Find Similar | Excel Worksheet Functions | |||
find similar numbers | Excel Discussion (Misc queries) | |||
need to correct scanned document all i get is jumbled words | Excel Discussion (Misc queries) | |||
Creating a Microsoft Words document from an existing Excel spreads | New Users to Excel |