Find text and return - NOOB question
 

I'm sure there's a simple answer for this, but I've searched for the better
part of 2 hours now and can't find what I'm looking for - any help will be
greatly appreciated! Anyway, I have 2 sheets that contain customer names -
one is a master list, the other new additions - I need to make sure there are
no customers on the new addition sheet that already exist on the master
sheet. Since the names may not be formatted the same, I used =LEFT to pull
out a portion of each name on the new additions sheet. What I can't figure
out is how to tell Excel to search the master sheet for the text in the
additions sheet.
Additions sheet:
F
2 ADVANCED I

Master sheet:
A
G
3580 123456 ADVANCED INDUSTRIAL SERVICES

So on the additions sheet I need to find ADVANCED I within column G on the
master and return the value for column A that corresponds to the name in G.

Did I evplain that okay?

Find text and return - NOOB question
 

Using a Vlookup on the additions sheet might be the answer...

Add a column on the left side of you master sheet with the left most 10
characters in the name.

On the additions sheet, use Vlookup to find the value in the first column of
the master sheet and return the value one column over(col_index_num = 2).
Make sure you set range_lookup to False to require an exact match. If the
addition is not in the master list, Vlookup will return #N/A.

Regards...

ChristopherTri



"rapid1" wrote:

I'm sure there's a simple answer for this, but I've searched for the better
part of 2 hours now and can't find what I'm looking for - any help will be
greatly appreciated! Anyway, I have 2 sheets that contain customer names -
one is a master list, the other new additions - I need to make sure there are
no customers on the new addition sheet that already exist on the master
sheet. Since the names may not be formatted the same, I used =LEFT to pull
out a portion of each name on the new additions sheet. What I can't figure
out is how to tell Excel to search the master sheet for the text in the
additions sheet.
Additions sheet:
F
2 ADVANCED I

Master sheet:
A
G
3580 123456 ADVANCED INDUSTRIAL SERVICES

So on the additions sheet I need to find ADVANCED I within column G on the
master and return the value for column A that corresponds to the name in G.

Did I evplain that okay?

Find text and return - NOOB question
 

The problem is the formatting Chris - the master sheet might say

THE CONSTRUCTION COMPANY

where the additions sheet might read

CONSTRUCTION COMPANY, THE

so I need to find CONSTRUCTION CO in the master sheet. I don't know how to
tell the VLOOKUP to do that?

"ChristopherTri" wrote:

Using a Vlookup on the additions sheet might be the answer...

Add a column on the left side of you master sheet with the left most 10
characters in the name.

On the additions sheet, use Vlookup to find the value in the first column of
the master sheet and return the value one column over(col_index_num = 2).
Make sure you set range_lookup to False to require an exact match. If the
addition is not in the master list, Vlookup will return #N/A.

Regards...

ChristopherTri

Find text and return - NOOB question
 

On Tue, 2 Jan 2007 10:01:02 -0800, rapid1
wrote:

I'm sure there's a simple answer for this, but I've searched for the better
part of 2 hours now and can't find what I'm looking for - any help will be
greatly appreciated! Anyway, I have 2 sheets that contain customer names -
one is a master list, the other new additions - I need to make sure there are
no customers on the new addition sheet that already exist on the master
sheet. Since the names may not be formatted the same, I used =LEFT to pull
out a portion of each name on the new additions sheet. What I can't figure
out is how to tell Excel to search the master sheet for the text in the
additions sheet.
Additions sheet:
F
2 ADVANCED I

Master sheet:
A
G
3580 123456 ADVANCED INDUSTRIAL SERVICES

So on the additions sheet I need to find ADVANCED I within column G on the
master and return the value for column A that corresponds to the name in G.

Did I evplain that okay?


Some variation of:

=IF(ISERR(SEARCH(A1,'Master List'!$A$1:$A$100)),
"Not on Master List","ON master list")

Should let you search an array to see if a particular string is present.

For A1, substitute the string you are searching for.


--ron

Find text and return - NOOB question
 

Hi Ron - that seems to net me the same thing - it appears to be looking at
the cell and not the text within the cell? I have been testing with data I
know is in both sheets and I cannot return any good info.

(A1) contains ADVAN & (B434) contains ADVANTAGE, what do I put in (C1) to
let me know that (B434) exists?

Ray


"Ron Rosenfeld" wrote:



Some variation of:

=IF(ISERR(SEARCH(A1,'Master List'!$A$1:$A$100)),
"Not on Master List","ON master list")

Should let you search an array to see if a particular string is present.

For A1, substitute the string you are searching for.


--ron

Find text and return - NOOB question
 

On Tue, 2 Jan 2007 12:54:00 -0800, rapid1
wrote:

Hi Ron - that seems to net me the same thing - it appears to be looking at
the cell and not the text within the cell? I have been testing with data I
know is in both sheets and I cannot return any good info.

(A1) contains ADVAN & (B434) contains ADVANTAGE, what do I put in (C1) to
let me know that (B434) exists?

Ray



=SEARCH(A1,B434)

will return a 1.

Or you could do something like:

=IF(ISNUMBER(SEARCH(A1,B434)),TRUE)

which will return TRUE.

If the contents of A1 are not found in B434, then the first formula will return
#VALUE!, the second would return FALSE.


However, if A1 is empty, or contains, for example, VAN, or TAG, or AD, or AGE,
then the formula will also return a 1.

You could easily test for A1 being empty.

How to find the "best match" is more difficult.
--ron

Find text and return - NOOB question
 

Hi Ron

Okay, I see that and see how it works. Now, how do I search a range? As in
the previous case, (A1) is ADVAN, (B434) is ADVANTAGE but I don't know that -
I need to search all of (B) to find (B434). Plus, there may be more than 1
occurrence of (A1).

Ray

"Ron Rosenfeld" wrote:

Ray



=SEARCH(A1,B434)

will return a 1.

Or you could do something like:

=IF(ISNUMBER(SEARCH(A1,B434)),TRUE)

which will return TRUE.

If the contents of A1 are not found in B434, then the first formula will return
#VALUE!, the second would return FALSE.


However, if A1 is empty, or contains, for example, VAN, or TAG, or AD, or AGE,
then the formula will also return a 1.

You could easily test for A1 being empty.

How to find the "best match" is more difficult.
--ron

Find text and return - NOOB question
 

It sound to me that you might be able to use a simple Vlookup with
wildcards, as long as you're only looking for a *single* match.

Say you have a list in Y1 to Z100, with *full* (complete) company names in
Y1 to Y100, and information pertaining to that company in Z1 to Z100.
You want to return that info from the second (Z) column.

You have an abbreviated name list in say A1 ... down to whatever.

In B1 try something like this:

=VLOOKUP("*"&A1&"*",$Y$1:$Z$100,2,0)

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"rapid1" wrote in message
...
Hi Ron

Okay, I see that and see how it works. Now, how do I search a range? As in
the previous case, (A1) is ADVAN, (B434) is ADVANTAGE but I don't know
that -
I need to search all of (B) to find (B434). Plus, there may be more than 1
occurrence of (A1).

Ray

"Ron Rosenfeld" wrote:

Ray



=SEARCH(A1,B434)

will return a 1.

Or you could do something like:

=IF(ISNUMBER(SEARCH(A1,B434)),TRUE)

which will return TRUE.

If the contents of A1 are not found in B434, then the first formula will
return
#VALUE!, the second would return FALSE.


However, if A1 is empty, or contains, for example, VAN, or TAG, or AD, or
AGE,
then the formula will also return a 1.

You could easily test for A1 being empty.

How to find the "best match" is more difficult.
--ron

Find text and return - NOOB question
 

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"rapid1" wrote in message
...
That's got it, thanks RD! I knew it had to be something simple!

Ray

"RagDyer" wrote:

It sound to me that you might be able to use a simple Vlookup with
wildcards, as long as you're only looking for a *single* match.

Say you have a list in Y1 to Z100, with *full* (complete) company names in
Y1 to Y100, and information pertaining to that company in Z1 to Z100.
You want to return that info from the second (Z) column.

You have an abbreviated name list in say A1 ... down to whatever.

In B1 try something like this:

=VLOOKUP("*"&A1&"*",$Y$1:$Z$100,2,0)

And copy down as needed.
--
HTH,

RD