Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find replace text or symbol with carriage return | New Users to Excel | |||
Find $ in a string of text and return numbers | Excel Discussion (Misc queries) | |||
how do i use VLOOKUP to find text and return a number? | Excel Discussion (Misc queries) | |||
Find certain text in a column and return statement | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |