Extract names from string based on value list

I have a list of productnames and a list of brandnames.

I want to supply the productnames with an attribute "Brand".

I am currently manually filtering the list of productnames based on 
"Contains... Brand" and adding the Brand to the product using copy paste, 
but this takes me too long and I can only match records one brand at a time.

Can I make Excell search through the list of productnames and whenever it 
comes across a brandname within the string of a productname it will add it to 
the column next to it?
0
Utf
12/3/2009 2:54:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
815 Views

Similar Articles

[PageSpeed] 56

One way
Assume productnames running in A2 down
Assume D2:D10 contains all the brands text 

In B2, normal ENTER:
=INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0))
Copy down to return required results

If you need an error trap to return neat looking blanks for unmatched cases 
(if any), use this in B2, normal ENTER, copied down:
=IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0)),"",INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0)))

Modify to suit. Voila? hit the YES below
-- 
Max
Singapore
xde
--- 
"Pluggie" wrote:
> I have a list of productnames and a list of brandnames.
> 
> I want to supply the productnames with an attribute "Brand".
> 
> I am currently manually filtering the list of productnames based on 
> "Contains... Brand" and adding the Brand to the product using copy paste, 
> but this takes me too long and I can only match records one brand at a time.
> 
> Can I make Excell search through the list of productnames and whenever it 
> comes across a brandname within the string of a productname it will add it to 
> the column next to it?
0
Utf
12/3/2009 3:21:01 PM
For the error trap, try this:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0))))

Only works with text.

-- 
Biff
Microsoft Excel MVP


"Max" <demechanik@yahoo.com> wrote in message 
news:F6044B1C-F218-44B0-A9CF-76CFB8AD4928@microsoft.com...
> One way
> Assume productnames running in A2 down
> Assume D2:D10 contains all the brands text
>
> In B2, normal ENTER:
> =INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0))
> Copy down to return required results
>
> If you need an error trap to return neat looking blanks for unmatched 
> cases
> (if any), use this in B2, normal ENTER, copied down:
> =IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0)),"",INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0)))
>
> Modify to suit. Voila? hit the YES below
> -- 
> Max
> Singapore
> xde
> --- 
> "Pluggie" wrote:
>> I have a list of productnames and a list of brandnames.
>>
>> I want to supply the productnames with an attribute "Brand".
>>
>> I am currently manually filtering the list of productnames based on
>> "Contains... Brand" and adding the Brand to the product using copy paste,
>> but this takes me too long and I can only match records one brand at a 
>> time.
>>
>> Can I make Excell search through the list of productnames and whenever it
>> comes across a brandname within the string of a productname it will add 
>> it to
>> the column next to it? 


0
T
12/3/2009 5:35:49 PM
Reply:

Similar Artilces:

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Indirect Method for Name Box Variable? #2
DARN The reason I want to do this is that I have to collect data on a dail basis but I have to display it on a graph on a weekly basis. I select the entire week of cells and give it a name like week52. The following week I have to select the entire week and name i week53. Everything is set up that I can just copy and the numbers will progres for whatever length of time I need to graph (months, years, etc) excep this. Maybe I'll just go back to using the range of cell numbers. Thanks guys -- Chris Brenna ----------------------------------------------------------------------- Chris Bre...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

If a worksheet name is = to test then a msgbox appears
I'm looking for a macro that will display a msgbox if a worksheet is = to test. For example, if the name of a sheet in a workbook is equal to test then display msgbox saying sheet already exists. Thanks Vick dim ws as worksheet set ws = nothing on error resume next set ws = worksheets("test") on error goto 0 if ws is nothing then msgbox "doesn't exist" else msgbox "already exists" end if Vick wrote: > > I'm looking for a macro that will display a msgbox if a worksheet is = to test. > > For example, if the name of a sheet in a w...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

y value vs x
In an xy scatter plot one can choose the type of line connecting the data points (smooth, straight, etc.). Once this is done, Is there a simple way of determining the y value of graph for a specific x value without doing successive approximations with 0 shifts. I'd rather not purchase a graphing program just for this simple task. You could find an equation that fits the data (see trendline) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ralph" <ralph@discussions.microsoft.com> wrote in message news:284B39DE-20C6-40CB-AB60-39B...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...

Combo Box initial values question
Does anyone know how to make a combo box show a value when a sheet opens? Mine are always blank when I open them until I select a value. thanks tp Hi Teepee, Try something like: Me.ComboBox1.ListIndex = 0 --- Regards, Norman "teepee" <teepee@noemail.com> wrote in message news:4645ed29$1@newsgate.x-privat.org... > Does anyone know how to make a combo box show a value when a sheet opens? > Mine are always blank when I open them until I select a value. > > thanks > > tp > > thanks for trying. says 'invalid use of me keyword.&...

List the UNIQUE certain fields from the database
Dear Experts, Please help related to the above subject. From this discussion, got the following formula which is work well and faster for small of database. =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) where $G$1, the first row of DATA. how to convert it to VBA as this time i have a huge database. for Tab Data, Advanced, Unique Record only (works well also, but i need the VBA). basically we would like to do similar like this: Data Sample Vendor Name A B...

distibution list / updating
when we update our contacts how can we get outlook to update our distribution lists at the same time. What do you mean exactly? Can you give an example? Also what version of Outlook are you using? T. >-----Original Message----- >when we update our contacts how can we get outlook to >update our distribution lists at the same time. >. > That's what the Update Now button is for. -- Russ Valentine [MVP-Outlook] "wayne" <wayne@anandasacramento.org> wrote in message news:05ba01c3d4be$e83a5e80$a301280a@phx.gbl... > when we update our contacts how can we g...

CRM Error
Hello When a user replies to an CRM email, clicks the "reply" button or the "reply all" button, clicks in the body of the email message and clicks "insert template", this error appears. This does not happen every time, and happens to various users. Does anyone know why we would get this error? ...

Print a list of movies
I have a spreadsheet with 2 columns (No and Name of Movie). There are 322 rows. I would like to print this so that I have the first 44 rows on the left side of the page and the next 44 rows on the right side of the page. I would like to keep this setup on all pages. These cells have color formatting and numbers that I need to keep with each cell. How do I do this? -- Terri Sprague www.tlsprague.com Hi Teri, See http://www.mvps.org/dmcritchie/excel/snakecol.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/...

interchangine catergory and values axis
How do I flip the catergory and value axis around so that the X axis becomes the catergory axis and the Y axis is the value axis? thanks 100gree Hi see: http://www.peltiertech.com/Excel/Charts/axes.html#SwitchXY -- Regards Frank Kabel Frankfurt, Germany "100green" <100green@discussions.microsoft.com> schrieb im Newsbeitrag news:95A7DE25-4535-4A48-8E6F-5D07F6DDE90F@microsoft.com... > How do I flip the catergory and value axis around so that the X axis > becomes > the catergory axis and the Y axis is the value axis? > thanks > 100gree Or http://peltiertec...

changing the application name
hi, i have developed a project and after completion of that project there is need to change that applicatin name. I will explained clearly i have created a document./view project , which named has "aaaa" afte completion the project, by running that application, there is a frame which has title "aaaa" Now there is need to change that title to "bbbbb" how can we do that? i am using VC++.net plz let me know how to do that by, koti "Koti" <koti@nannacomputers.com> wrote in message news:OqHUbvNQFHA.1236@TK2MSFTNGP14.phx.gbl... >...

How to export users list from MS-Exchange AD Users&Computers?
Hello, I want to export my exchange mailbox-enabled users list with their Given Name+Surname and e-mail address.. I get this kind of list from ActiveDirectory Users&Computers but I can not export it.. I know that there is a program somewhere that does what I want, but I can't remember it's name.. Note: I don't mean LDIFDE tools.. Thanks for your help. Capricorn As always...be careful and test things in a lab first, please. LDIFDE tools can be pretty ugly... CSVDE will work and it is possible to limit the columns that are output by CSVDE. Note: If you have a large ...