Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Two Columns - Multiple Words in Same Same Word
I am trying to compare two groups of words together and would like to
be able to do the following: Compare group 1 words against group 2 words and create and identify any words in group 2 that contain any of the terms (including multiple terms)from group 1. For instance: Group 1 Group 2 Identified Words one one one two nine onethree three eight four onethree I have used this (in column c) to identify single words: =COUNTIF(A:A,b1) =COUNTIF(A:A,b2) =COUNTIF(A:A,b3) and so on... .....but that's not quite what I'm looking for. Any help would be greatly appreciated. Many Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Two Columns - Multiple Words in Same Same Word
I think I'd use a helper column that gives me an indicator and then use
data|filter|autofilter to get the ones that matched: I put your data in A2:B5 (allowed for headers) and put this in C1: =IF(COUNT(SEARCH($A$2:$A$5,B2))0,"Used","not used") Hit ctrl-shift-enter instead of just enter. This is an array formula. If you do it correctly, excel will wrap curly brackets {} around your formula. Adjust the range and drag it down. Then you can use Data|Filter|Autofilter to see the Used or unused list. Blah wrote: I am trying to compare two groups of words together and would like to be able to do the following: Compare group 1 words against group 2 words and create and identify any words in group 2 that contain any of the terms (including multiple terms)from group 1. For instance: Group 1 Group 2 Identified Words one one one two nine onethree three eight four onethree I have used this (in column c) to identify single words: =COUNTIF(A:A,b1) =COUNTIF(A:A,b2) =COUNTIF(A:A,b3) and so on... ....but that's not quite what I'm looking for. Any help would be greatly appreciated. Many Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Two Columns - Multiple Words in Same Same Word
=IF(COUNTIF($A$2:$A$5,"*"&B2&"*"),"Used","not used")
Should also work and uses fewer functions. Not an array formula. Assumes there is a string to test in the cell referred to in the second argument (B2 above). for your example with a double word in column B, it should probably be reversed =If(countif($b$2:$B$5,"*"&A2&"*"),"Used","Not Used") Regards, Tom Ogilvy Dave Peterson wrote in message ... I think I'd use a helper column that gives me an indicator and then use data|filter|autofilter to get the ones that matched: I put your data in A2:B5 (allowed for headers) and put this in C1: =IF(COUNT(SEARCH($A$2:$A$5,B2))0,"Used","not used") Hit ctrl-shift-enter instead of just enter. This is an array formula. If you do it correctly, excel will wrap curly brackets {} around your formula. Adjust the range and drag it down. Then you can use Data|Filter|Autofilter to see the Used or unused list. Blah wrote: I am trying to compare two groups of words together and would like to be able to do the following: Compare group 1 words against group 2 words and create and identify any words in group 2 that contain any of the terms (including multiple terms)from group 1. For instance: Group 1 Group 2 Identified Words one one one two nine onethree three eight four onethree I have used this (in column c) to identify single words: =COUNTIF(A:A,b1) =COUNTIF(A:A,b2) =COUNTIF(A:A,b3) and so on... ....but that's not quite what I'm looking for. Any help would be greatly appreciated. Many Thanks! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Two Columns - Multiple Words in Same Same Word
Thanks so much for your help guys. Unforunately I've had to revise my
question. Here is what I've posted on another string (I'm such a newbie... I created two strings by accident) - FIRST REVISION Thanks so much! It works very well! Is there still a way to get the exact word too? Example: Get the "one" and the "onethree" from the last example? Secondly, I have now run into an issue that the "group 1" list now spans 2 columns (a and b - yes it's that big... and no I can't really use a db :-). So, I would now like to compare "c" against "a" and "b" and display in "d". Please note that I would still need to look for combinations from "a" and "b" in one word. Example: "A" "B" "C" Identified Words ("D") one eight one one two nine nine onethree three ten eight tenfour four onethree tenfour Also, please assume I will need to reference the entire "a" &"b" rows against "c". SECOND REVISION Oh yeah. One other thing. Column "d" (sorry, i said row "d" before) should only display results that are exactly a combination (or single) match with "a" and "b" when referencing "c". Example: In this example, "onenight" would not appear in column "d" because "night" is not listed in column "a" or "b" "A" "B" "C" Identified Words ("D") one eight one one two nine nine onethree three ten eight tenfour four onethree tenfour onenight Thanks again for your help! "Tom Ogilvy" wrote in message ... =IF(COUNTIF($A$2:$A$5,"*"&B2&"*"),"Used","not used") Should also work and uses fewer functions. Not an array formula. Assumes there is a string to test in the cell referred to in the second argument (B2 above). for your example with a double word in column B, it should probably be reversed =If(countif($b$2:$B$5,"*"&A2&"*"),"Used","Not Used") Regards, Tom Ogilvy Dave Peterson wrote in message ... I think I'd use a helper column that gives me an indicator and then use data|filter|autofilter to get the ones that matched: I put your data in A2:B5 (allowed for headers) and put this in C1: =IF(COUNT(SEARCH($A$2:$A$5,B2))0,"Used","not used") Hit ctrl-shift-enter instead of just enter. This is an array formula. If you do it correctly, excel will wrap curly brackets {} around your formula. Adjust the range and drag it down. Then you can use Data|Filter|Autofilter to see the Used or unused list. Blah wrote: I am trying to compare two groups of words together and would like to be able to do the following: Compare group 1 words against group 2 words and create and identify any words in group 2 that contain any of the terms (including multiple terms)from group 1. For instance: Group 1 Group 2 Identified Words one one one two nine onethree three eight four onethree I have used this (in column c) to identify single words: =COUNTIF(A:A,b1) =COUNTIF(A:A,b2) =COUNTIF(A:A,b3) and so on... ....but that's not quite what I'm looking for. Any help would be greatly appreciated. Many Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare 2 columns with words to 1 and to get the 3rd word. | Excel Discussion (Misc queries) | |||
Comparing value in multiple columns | Excel Discussion (Misc queries) | |||
comparing multiple columns | Excel Worksheet Functions | |||
comparing multiple columns | Excel Worksheet Functions | |||
Comparing multiple columns | New Users to Excel |