looking for range of text in a single cell

I just started a new job and my company already had a file with abou
16,000 records in it (called Products).  This file contains all of th
parts numbers that they carry.  In this file there is one column calle
"description".  This cell contains the dimensions, color name and som
other information such as if the piece is flat or rounded.  I hav
another file that has about 400 records and this file tells me "colo
name" as well as the "type" of stone that it is (called Stone_Type).

What I am trying to do is add a field to the Products file that wil
say stone type.  Is there a way to have Excel search the "description
from Products for "color name" from Stone_Type and if it finds i
return "type"?  As previously mentioned "description" contains thing
other than just the color name.  To further complicate things there i
no consistency as to where in the cell the color name is (meaning 
cannot extract just that data).  Most of the color names are more tha
one work long.

If the "description" field matched "color name" I could just do 
vlookup and return "type" but with there being other data in the fiel
I don't know what to do.  I thought about putting each work in its ow
column and doing a whole bunch of vlookups but the problem with that i
that most of the names are more than one word long.  What I am thinkin
about now is using the find function to look for the "color name" i
"description" and if it finds it then return the cell reference so tha
I can run a vlookup off of that.  I think this will work but I don'
know how to make it search all of the "color name�s  (A2:A400 i
Stone_Type).  

Does anyone know how I could do this or know of a better way to g
about it?  As stated above I have 16,000 record I have to do this to s
I am sure this will take a very long time to run but once it is done th
first time that is it and I can hard code the values so I do not reall
care how long it takes.

Thank you.  I am not sure if I did a decent job explaining this i
there are any questions please ask and I will try to respond a.s.a.p

--
hatte
-----------------------------------------------------------------------
hatter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2676
View this thread: http://www.excelforum.com/showthread.php?threadid=40015

0
8/29/2005 5:23:34 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
427 Views

Similar Articles

[PageSpeed] 0

Reply:

Similar Artilces:

"unable to display all the cells due to low memory"
I have a co-worker getting this error whenever I try to open my Tasks folder. He has a lot of tasks (1000+), but they are all very small with no attachments. Running Outlook 2000 on Exchange 2000. Thanks! -- Remove 'spam' from email address to contact me directly ...

how do i automatically archive old cell contents to a separate .
I have a worksheet that contains lists of "action items", that is used to keep track of jobs that need to be done. I want to be able to automatically move the contents of a row of cells to another worksheet once the action item has been completed. In other words, once I have changed the value in a cell to "yes" (the action has been completed), the information is archived in a separate worksheet. Is this possible, and how? I'd keep them in place, but use Data|Filter|autofilter to show/hide what I want. I think it makes life much more simple--especially when you...

Change text to uppercase
Is there a way to format a cell so that if a user types text in the cell in lower case, Excel will automatically change it to uppercase. Any help will be greatly appreciated. Thanks in advance. Matt something like this would work for a single cell Code ------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Target.Value = UCase(Target.Value) End Sub ------------------- Place the code in the relevant worksheet code modul -- Message posted from http://www.ExcelForum.com Matt, you can do it with a macro, here is one way,...

Max Function
Hi there I have 2 columns. Column A has text (either a P, C, NE) and Column B has numbers. I am trying to find the max number in Column B that has a P entry. Is there a way to do this. Array enter using ctrl+shift+enter =MAX(IF(a1:a100="p",b1:b100)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <calebmichaud@gmail.com> wrote in message news:c8b69656-a376-4933-a5f6-c81c4d7a51ec@o27g2000vbd.googlegroups.com... > Hi there > > I have 2 columns. Column A has text (either a P, C, NE) and Column B > has numbers. > I am trying to ...

instantaneous cell value
This is probably incredibly simple but I've been going in circles with it for some time. I'm looking for a way to grab and hold the instantaneous value of a cell whose contents are constantly being updated. Any thoughts? You can "fix" the values of a cell by <copy>, then select another cell (maybe in another sheet) and Edit | Paste Special | Values | OK. This would leave the formula in the original cell to continue to update itself. Hope this helps. Pete thanks for the reply. this works as a "manual" solution but how can I assign only the contents o...

Issue with cell ranges and formulas
I've got an interesting issue that I'd like some help with. I'm doing a performance analysis where I'm trying to average some numbers. Here's what I did to create the worksheet: In cell A1 enter 'Time', in B1 enter 'CPU #0', in C1 enter 'CPU #1', in D1 enter 'CPU #2', in E1 enter 'CPU #3', and in F1 enter 'Average'. I then format the cells in column A to use the 'time' category and '1:30 PM' type. Now I enter in cell A2 '8:30 PM' and A3 with '8:35 PM'. Next enter '17.368' in ...

Aggregating several public folders into a single view
I have many public folders I need to monitor for changes. I would like a single view containing posts in reverse chronological order for all the folders of interest. That way I don't have to click through each folder. Any thoughts on how to achieve this? Can it be done without programming? Doug ...

Splitting cells into multiple records
Hello, Here is what I am trying to do. I have an access DB that Excels pulls info out of and puts in a nice little format. My issue is that some of the fields have multiple records in it. Here is an example of the DB: First Name | Last Name | Issue | Remediation You would only have one first and last name, but there might be multiple instances in the issue and remediation cells. First Name | Last Name | Issue | Remediation John |Smith | Issue Number 1 | Remediation Number 1 | Issue Number 2 | Remediation Nu...

VBA: Can't set ChartTitle.Text??
My macro tells me it can't set the ChartTitle. "Unable to set the Text property of the ChartTitle class". Any help. please? Ed Sub MakeNewScatterChart() Dim wkb As Workbook Dim wks As Worksheet Dim LastRow As Long Dim LastCol As Long Dim colData As Long Dim cht As Chart Dim chtObj As ChartObject Dim rngData As Range Dim rngXval As Range Dim rngSer As Range Dim strTitle As String ' Get limits of data Set wkb = ActiveWorkbook Set wks = wkb.Sheets(1) LastRow = wks.Range("A65536").End(xlUp).Row LastCol = wks.Range("IV4").End(xlToLeft).Column ' Set d...

Filtered cells return after save???
Excel 2007... After removing columns from a document Im left with rows that have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of...

All incoming e-mail has a .txt attachment with message text
My problem seems to be the same as Rob-J. Starting this morning, all incoming e-mail received into Outlook 2000 (with SP-3 applied) now has both the regular incoming text, plus a .txt attachment with the exact same text in it. I have made no changes to any Outlook settings or Windows XP settings. Of course, some whacko keystroke may have occurred unbeknownst to me and done something, but of course I can't know that. Now for the really interesting part. I also have Outlook 2000 with SP-3 applied also installed on my laptop. It does NOT experience this problem! I have compare...

How to combine text from 3 cells into 1 cell
How to combine from 3 text-cells into 1 cell with space between them? See your other post for answer. John "JOF" <JOF@discussions.microsoft.com> wrote in message news:35A395E5-81A3-428E-BED7-1DF41F5370FF@microsoft.com... > How to combine from 3 text-cells into 1 cell with space between them? =A1&" "&B1&" "&C1 or =CONCATENATE(A1," ",B1," ",C1) "JOF" wrote: > How to combine from 3 text-cells into 1 cell with space between them? try this if your text are in a column A in B2 =A2&" "&...

Text to Date Conversion
This is probably an age old question, so apologies in advance... I'm trying to import data into Excel from a csv file that contains a date field of the format "Mon 01 Jun". There seems to be no easy way to get Excel to recognise this as a date, unless anyone can tell me differently. Assuming there isn't a quick import / text-to-columns conversion, what is the easiest way with a function to create a new cell that contains a date from this text? Thanks, Richard. With the text in A1; assuming all months are 3 characters =DATE(2009,MATCH(RIGHT(A1,3),{"Jan",&q...

Write scatterplot point ID to a cell
Hello all I'm trying to click a point in a scatterplot and have its ID (label, values, whatever) written to a cell. Any ideas? Thanks jcamoes Hi, Have a look at Jon Peltier's article "Chart Events in Microsoft Excel" http://www.computorcompanion.com/LPMArticle.asp?ID=221 Cheers Andy jcamoes wrote: > Hello all > > I'm trying to click a point in a scatterplot and have its ID (label, > values, whatever) written to a cell. Any ideas? > > Thanks > > jcamoes > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks! That&#...

a good algorithm for sorting texts?
Hi, I am trying to find a good sorting algorithm to sort words in a sentence alphabetically? can anyone give me some help? many thanks DAXU wrote: > Hi, > I am trying to find a good sorting algorithm to sort words in a > sentence alphabetically? can anyone give me some help? Array.Sort() is a good place to start. Then, you might look at List<T>.Sort(). http://msdn.microsoft.com/en-us/library/system.array.sort.aspx http://msdn.microsoft.com/en-us/library/3da4abas.aspx If you can define "words" better, then it's possible a suggestion can be pro...

Formula to copy multiple cells onto another sheet
I have a worksheet containing names and address that are repeated onto other sheets based on criteria in another column. What formula if any can I use to determine if that person meets that certain criteria to automatically copy the 3 columns that the name, phone, and address are located in to another sheet? A little more info: I have multiple worksheets and one master list containing 6 columns: First, Last, Birthday,Phone, and Address, and class. In the class column is a variety of class names. I would like to write a macro (I have never done this before!) ,if possible, that will ...

Office 2007 Styles create issue with "Too many different cell form
I'm not sure if anyone has come across this issue. I have a file that is used by both 2007 and 2003 users. Everything was working fine until one day the 2003 users were getting "Too many different cell formats." The issue was that there were now 3000+ styles on the spreadsheet. There were now custom styles created for each of the new cell styles in 2007 like the accents. It looks like everytime this was saved in compatibility mode, this was turned into a custom style. Eventually there were Accent1, Accent 1 1, Accent 1 2, etc. This looks like it will continue to...

Sticking cells
BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 and 2 on my spreadsheet to 'stick', so when I scroll down my sheet the top 2 rows are always at the top. How can I do this? Thanks in advance. -- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" "Laura ( '_' )" <laura@invalid.example.com> wrote in message news:u%235COkd8FHA.4012@TK2MSFTNGP14.phx.gbl... > BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 > and > 2 on my spreadsheet to 'stick', so when...

Insert Cell Text Into Chart
I have a cell of text(on sheet 1) that I want to paste into the top portion of a chart (on sheet 2) - I have done this before, but I cannot remember how - HELP! Hi insert a textbox, select this textbox and enter the equation sign '='. Now select your cell on sheet 1 and hit ENTER -- Regards Frank Kabel Frankfurt, Germany Karen wrote: > I have a cell of text(on sheet 1) that I want to paste > into the top portion of a chart (on sheet 2) - I have done > this before, but I cannot remember how - HELP! Thank you for your help - I'm sorry, maybe I didn't explain it ...

Look up multi values
I’m struggling figuring out how you would do the following? Work sheet data. A1 =25 B1 = 40 C D 1 25 30 2 25 40 3 27 41 4 28 42 What I want to do is lookup the values 25 & 40 (A1 & B1) from table C1 D4 Matching the value 25 in Column C & 40 in Column D & return the location in the table to Cell A2? If there is no matching value, were decimals come into play, i.e. 25.8, then return the next largest from column C with its parent value from D, Returned value = 2 or 3 if 25.5. Sounds simple but I get lost very quick! Any Help greatly appreciated. one way. This is an array...

Omitting hidden columns when copying a range from a worksheet
I have a worksheet with numerous hidden columns and rows. I would like to copy the visible cells to a new worksheet, omitting the hidden cells. However, using cut and paste or paste special I always get the hidden cells copied as well. Is there a way round this? Alternatively, is there a quick way of deleting all hidden rows & columns, which would allow me to achieve the same thing if I copy the whole worksheet and then delete hidden rows & columns? After you've selected your range with the hidden rows/columns, hit Edit|goto (or F5 or ctrl-G) and the click special, then selec...

How to change the active cell outline indicator color in Excel?
I want to change the outline color of the active cell indicator to something other than black, so that it is more distinct on screen, since I often have black gridlines in my worksheets. How can this be done? maybe.... you may want to try Chip Pearson's Rowliner: http://www.cpearson.com/excel/RowLiner.htm YoYo74 wrote: > > I want to change the outline color of the active cell indicator to something > other than black, so that it is more distinct on screen, since I often have > black gridlines in my worksheets. How can this be done? -- Dave Peterson I had a need for...

Kill debug for single module
Hi, I am having a problem with a certain DLL (thats basically littered with badly placed interupts / breakpoints) and I would like to disable the debugger picking them up for just the one DLL. Is this possible? if so would someone explain to me how to acomplish this in Visual Studio 2005. Cheers -- - Mark Randall http://www.temporal-solutions.co.uk "We're Systems and Networks..." "It's our job to know..." "Mark Randall" <mark[__OKTHISISFAKE_]yr@REMOVETHISgoogle.ANDTHIScom> wrote in message news:%23HLSnI5pGHA.3288@TK2MSFTNGP03.phx.gb...

universal formatting of all cells in all worksheets
I have 140 sheets in an excel document. In column AF I want the width to be set to 15.86. Instead of going to every sheet and making the change, is there a way that I can set the width for that column in all of the sheets right click on a sheet tab>select all sheets>set whatever on the sheet you are on>select any One sheet. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "jeremyrod" <jeremyrodriguez@cmsmechanical.com> wrote in message news:0b4301c351e9$39fe41e0$a501280a@phx.gbl... > I have 140 sheets in an excel document. In column AF I >...

Will excel 2003 format a cell to the year?
It seems like it should be obvious, but I am finding it impossible to get excel to do a date format that recognizes a year. Is there some way? I typed a date in A1 (09/28/2006) and used format|Cells|number tab|custom category|yyyy with my USA settings. kateofmd@msn.com wrote: > > It seems like it should be obvious, but I am finding it impossible to > get excel to do a date format that recognizes a year. Is there some > way? -- Dave Peterson ...