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

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

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

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

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

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

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

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