Find a string within a column
 

I have two columns of data and I want to know where column one's data exists
in column two. It is not as easy as just using a countif function or
anything. Please see my data set. Column C is just used to see if it
exists. Please help.

A B C
Jason Hopson Jason Found in Column A is yes
Jack Alan Sam Found in Column A is yes
Mark Bran Noelia Found in Column A is yes
Noelia Sam Tina Not found
Sam Zink

Find a string within a column
 

In C1 put this and copy down..........

=IF(COUNTIF(A:A,"*"&B1&"*")0,"Found in column A is yes","Not found")

Vaya con Dios,
Chuck, CABGx3



"Rookie_User" wrote:

I have two columns of data and I want to know where column one's data exists
in column two. It is not as easy as just using a countif function or
anything. Please see my data set. Column C is just used to see if it
exists. Please help.

A B C
Jason Hopson Jason Found in Column A is yes
Jack Alan Sam Found in Column A is yes
Mark Bran Noelia Found in Column A is yes
Noelia Sam Tina Not found
Sam Zink

Find a string within a column
 

That could return false positives:

Eric Sampson............Sam
Alice Markson...........Mark

A little more robust: (but still not 100% bulletproof)

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" "," "&A$1:A$10&"
")))),"Yes","No")

Biff

"CLR" wrote in message
...
In C1 put this and copy down..........

=IF(COUNTIF(A:A,"*"&B1&"*")0,"Found in column A is yes","Not found")

Vaya con Dios,
Chuck, CABGx3



"Rookie_User" wrote:

I have two columns of data and I want to know where column one's data
exists
in column two. It is not as easy as just using a countif function or
anything. Please see my data set. Column C is just used to see if it
exists. Please help.

A B C
Jason Hopson Jason Found in Column A is yes
Jack Alan Sam Found in Column A is yes
Mark Bran Noelia Found in Column A is yes
Noelia Sam Tina Not found
Sam Zink

Find a string within a column
 

WOW, that is so awesome thank you. Now that you got me 95% of the way there
can you maybe make one more modifcation to accomplish the next iteration.
The same situation above, heres the data, I changed it from above but I just
need to do one more iteration to "tune" our numbers. So, in this example

Quotes Column A Order -Column B

Amy Woo ~-060251-a 113028-Herb Mills ICC
Adams Dr Art ~-060229-a 113283-Dr Jason Daaboul ICC
Alan Mills ~-050810-d 113376-Hortman ICC
Allan-Dr. Finney ~-050510-b 113401-HedrickDodson ICC
Abbot ~-060174-a 113422-JVH-Cooper-06 ICC
Advanced Endodontics-060171-a 113466-Lacy ICC
Arrington ~-060152-a 113496-Drs Cross-Wan Burns ICC

Even though colB will not exactly match ColA - if any part of the cell
contents in column b matches any part of the cell contents in column A I need
to know. Is there a way to do this. Additionally, if you have time can you
explain your answer, your formula is awesome you gave me before but I can't
understand it. If it takes too much time don't worry about it. I am trying
to get these results early this moring PST time.
"Biff" wrote:

That could return false positives:

Eric Sampson............Sam
Alice Markson...........Mark

A little more robust: (but still not 100% bulletproof)

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" "," "&A$1:A$10&"
")))),"Yes","No")

Biff

"CLR" wrote in message
...
In C1 put this and copy down..........

=IF(COUNTIF(A:A,"*"&B1&"*")0,"Found in column A is yes","Not found")

Vaya con Dios,
Chuck, CABGx3



"Rookie_User" wrote:

I have two columns of data and I want to know where column one's data
exists
in column two. It is not as easy as just using a countif function or
anything. Please see my data set. Column C is just used to see if it
exists. Please help.

A B C
Jason Hopson Jason Found in Column A is yes
Jack Alan Sam Found in Column A is yes
Mark Bran Noelia Found in Column A is yes
Noelia Sam Tina Not found
Sam Zink

Find a string within a column
 

Even though colB will not exactly match ColA - if any part of the cell
contents in column b matches any part of the cell contents in column A I
need
to know. Is there a way to do this.

Not that I know of. You can't be that general, "if any part matches any
part". You have to search for specific substrings.

Based on the last sample you posted I didn't see anything that even closely
matched. (other than DR and I'm guessing that isn't a match)

can you explain your answer

The formula just searches for entire words that have a space before and
after. That way, Mark and Markson can't be a match.

If the cell contents was:

Mark Adams

There obviously isn't a space before Mark so the formula concatenates a
space to the beginning and to the end of the cells contents.

Biff

"Rookie_User" wrote in message
...
WOW, that is so awesome thank you. Now that you got me 95% of the way
there
can you maybe make one more modifcation to accomplish the next iteration.
The same situation above, heres the data, I changed it from above but I
just
need to do one more iteration to "tune" our numbers. So, in this example

Quotes Column A Order -Column B

Amy Woo ~-060251-a 113028-Herb Mills ICC
Adams Dr Art ~-060229-a 113283-Dr Jason Daaboul ICC
Alan Mills ~-050810-d 113376-Hortman ICC
Allan-Dr. Finney ~-050510-b 113401-HedrickDodson ICC
Abbot ~-060174-a 113422-JVH-Cooper-06 ICC
Advanced Endodontics-060171-a 113466-Lacy ICC
Arrington ~-060152-a 113496-Drs Cross-Wan Burns ICC

Even though colB will not exactly match ColA - if any part of the cell
contents in column b matches any part of the cell contents in column A I
need
to know. Is there a way to do this. Additionally, if you have time can
you
explain your answer, your formula is awesome you gave me before but I
can't
understand it. If it takes too much time don't worry about it. I am
trying
to get these results early this moring PST time.
"Biff" wrote:

That could return false positives:

Eric Sampson............Sam
Alice Markson...........Mark

A little more robust: (but still not 100% bulletproof)

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" "," "&A$1:A$10&"
")))),"Yes","No")

Biff

"CLR" wrote in message
...
In C1 put this and copy down..........

=IF(COUNTIF(A:A,"*"&B1&"*")0,"Found in column A is yes","Not found")

Vaya con Dios,
Chuck, CABGx3



"Rookie_User" wrote:

I have two columns of data and I want to know where column one's data
exists
in column two. It is not as easy as just using a countif function or
anything. Please see my data set. Column C is just used to see if it
exists. Please help.

A B C
Jason Hopson Jason Found in Column A is yes
Jack Alan Sam Found in Column A is yes
Mark Bran Noelia Found in Column A is yes
Noelia Sam Tina Not found
Sam Zink

Find a string within a column
 

This still doesn't do what you really want, I'm trying to do basically
the same thing, but here's another partial solution. Perhaps someone
might have the answer.


Step 1 - Data in original format: Need to extract the Upper Case
names and input them into a the next column.

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

Step 2

Column1 1 is the original data. I need to pull a specific string out
of each of the cells:
and put them in to Column2. What is below would be the ideal result,
but what I can now do is only one item, "BOB" for exaple, at a time and
I would need to copy the result for BOB to a 3rd column and then run the
formula again for the next case "DICK" but before that is done, manually
cut & paste BOB to another column then run through the procedure for BOB
DICK HARRY & TOM. Very tedious.

One of the formulas suggested easily extracts the data, but I still
have to cut & paste.

column1 column2 column3 column4
acBOBee BOB rem achieved through
=IF(ISNUMBER(SEARCH("BOB",Col1)),"BOB","")
eDICKrt DICK rem achieved through
=IF(ISNUMBER(SEARCH("DICK",Col1)),"DICK","")
oTOMidk TOM
pHARRYw HARRY
dfBOBrr BOB
rtBOBkj BOB
kTOMrrq TOM

This looks to be the same task that Rookie_User is seeking.

I also thought of matching a value in a 3rd & 4th column, and can do it
with exact matchs, but not when looking at a portion of a string in a
larger string.

Formula would be

=INDEX(col2 ,match(cell col4,range col3))

Col 2 would be the pool of unique data
cell in col4 is the look up
Col4 is match

I'll post this in Rookie_User as well.

Thanks

Ric D


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=522756

Find a string within a column
 

This still doesn't do what you really want, I'm trying to do basically
the same thing, but here's another partial solution. Perhaps someone
might have the answer.


Step 1 - Data in original format: Need to extract the Upper Case
names and input them into a the next column.

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

Step 2

Column1 1 is the original data. I need to pull a specific string out
of each of the cells:
and put them in to Column2. What is below would be the ideal result,
but what I can now do is only one item, "BOB" for exaple, at a time and
I would need to copy the result for BOB to a 3rd column and then run the
formula again for the next case "DICK" but before that is done, manually
cut & paste BOB to another column then run through the procedure for BOB
DICK HARRY & TOM. Very tedious.

One of the formulas suggested easily extracts the data, but I still
have to cut & paste.

column1 column2 column3 column4
acBOBee BOB rem achieved through
=IF(ISNUMBER(SEARCH("BOB",Col1)),"BOB","")
eDICKrt DICK rem achieved through
=IF(ISNUMBER(SEARCH("DICK",Col1)),"DICK","")
oTOMidk TOM
pHARRYw HARRY
dfBOBrr BOB
rtBOBkj BOB
kTOMrrq TOM

This looks to be the same task that Rookie_User is seeking.

I also thought of matching a value in a 3rd & 4th column, and can do it
with exact matchs, but not when looking at a portion of a string in a
larger string.

Formula would be

=INDEX(col2 ,match(cell col4,range col3))

Col 2 would be the pool of unique data
cell in col4 is the look up
Col4 is match

I'll post this in Rookie_User as well.

Thanks

Ric D


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=522756

Find a string within a column
 

This still doesn't do what you really want, I'm trying to do basically
the same thing, but here's another partial solution. Perhaps someone
might have the answer.


Step 1 - Data in original format: Need to extract the Upper Case
names and input them into a the next column.

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

Step 2

Column1 1 is the original data. I need to pull a specific string out
of each of the cells:
and put them in to Column2. What is below would be the ideal result,
but what I can now do is only one item, "BOB" for exaple, at a time and
I would need to copy the result for BOB to a 3rd column and then run the
formula again for the next case "DICK" but before that is done, manually
cut & paste BOB to another column then run through the procedure for BOB
DICK HARRY & TOM. Very tedious.

One of the formulas suggested easily extracts the data, but I still
have to cut & paste.

column1 column2 column3 column4
acBOBee BOB rem achieved through
=IF(ISNUMBER(SEARCH("BOB",Col1)),"BOB","")
eDICKrt DICK rem achieved through
=IF(ISNUMBER(SEARCH("DICK",Col1)),"DICK","")
oTOMidk TOM
pHARRYw HARRY
dfBOBrr BOB
rtBOBkj BOB
kTOMrrq TOM

This looks to be the same task that Rookie_User is seeking.

I also thought of matching a value in a 3rd & 4th column, and can do it
with exact matchs, but not when looking at a portion of a string in a
larger string.

Formula would be

=INDEX(col2 ,match(cell col4,range col3))

Col 2 would be the pool of unique data
cell in col4 is the look up
Col4 is match

I'll post this in Rookie_User as well.

Thanks

Ric D


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=522756

Find a string within a column
 

I posted a similar request and got this reply from Ron Rosenfeld. Try
it out, it worked for me and it might be what you're looking for. It
does require a freeware add-in. You might also check out my post:
http://www.excelforum.com/showthread.php?t=521909

To pull out any of a list of words from your column 1, you could use
the
formula:

=REGEX.MID(A1,"(BOB)|(TOM)|(DICK)|(HARRY)")

column1
acBOBee
eDICKrt
oTOMidk
pHARRYw
dfBOBrr
rtBOBkj
kTOMrrq

To make the list more manageable, you could put the names in a range
named rng
(one name per cell) and use the formula:

=REGEX.MID(A1,MCONCAT(rng,"|"))

To use the above formulas, you must download and install Longre's free
morefunc.xll add-in from:

http://xcell05.free.fr


--
ricxl
------------------------------------------------------------------------
ricxl's Profile: http://www.excelforum.com/member.php...o&userid=32412
View this thread: http://www.excelforum.com/showthread...hreadid=522756