Hi,
Im trying to make some kind of automatic analysis formula for one column (lets say A) that has text and numbers strings.
Example:
A1 - resistor 10K 1% 0.5W 0402
A2 - resistor 20K 1% 0.5W 0603
A3 - resistos 30K 1% 0.5W 0805
A4 - resistor 20K 0.5% 0.25W 0402
and I need to evaluate IF A* contains "res*" AND "1%" AND "0402" - if so - B* should state "res 0402" as true condition
AND IF A* contains "res*" AND "1%" AND "0603" - if so - B* should state "res 0603" as true condition
AND IF A* contains "res*" AND "1%" AND "0805" - if so - B* should state "res 0805" as true condition
so I should get results:
B1 - res 0402
B2 - res 0603
B3 - res 0805
A4 - blank or false
if one of conditions is not followed it can state "false" or nothing, it doesnt matter.
for single "pack" of 3 conditions I used such formula:
"=IF(AND(ISNUMBER(SEARCH("RES",A1)),ISNUMBER(SEARCH("1%",A1)),ISNUMBER(SEARCH("0402",A1))),"RES 1% 0402","False")"
but I dont know know how to integrate all following options for checking 0603 and 0805 and so on in column A.
maybe this should be switched to VBA, not only formulas?..
any ideas?
thanks!
Last edited by flyman; 01-26-2012 at 07:17 AM.
Would this be a possible solution?
In B1
Drag/Fill Down=IF(AND(ISNUMBER(SEARCH("res",A1,1)),ISNUMBER(SEARCH(" 1%",A1,1))),"res "&RIGHT(A1,4),"")
It doesn't specifically check for 0402, 0603, 0805, rather it only ckecks for "res" and " 1%".
Failing that, then
=IF(AND(ISNUMBER(SEARCH("res",A1,1)),ISNUMBER(SEARCH(" 1%",A1,1)),OR(ISNUMBER(SEARCH("0402",A1,1)),ISNUMBER(SEARCH("0603",A1,1)),ISNUMBER(SEARCH("0805",A1,1)))),"res "&RIGHT(A1,4),"")
Last edited by Marcol; 01-09-2012 at 06:47 AM.
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
well, first option is not checking 0603 or 0805 and gives too many results, while second option would be good IF it would be a general rule that 0603/0805/0402 would be last characters of a string in A1. My example maybe is too precise, but at some point string could be in a mixed condition:
10K 0603 1% 0.5W RES
so basically "right" function is not going there.
thats the trick, basically I need to specify several possible output text results for several condidtion packages.
but in perfect conditions, when 0603 is the last characters - second offer is working and giving good output.
Let's get rid of the "trick" element.
How about posting a workbook with typical data and your expected result, and not a "perfect world" scenario?
Sure, no problem.
I attached file with some usuall data and all scenarious that I am trying to include into analysis.
Hope this makes everything more clear.
Pl see the attached file with macro.
Last edited by kvsrinivasamurthy; 01-11-2012 at 12:44 AM.
sorry kvsrinivasamurthy, I see no file attached.. Where is it hidden?Originally Posted by kvsrinivasamurthy
Look again.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
OK, thanks, it works for my example.
Lets make it more flexible:
I tried putting everything in IF formulas and this is what I got:
=IF(AND(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("0402",C3))),"0.0005",I F(AND(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("0603",C3))),"0.0005",IF( AND(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("0805",C3))),"0.0009",IF(AN D(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("1206",C3))),"0.0014",IF(AND( ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("X7R",C3)),ISNUMBER(SEARCH("04 02",C3))),"0.0018",IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("X7R ",C3)),ISNUMBER(SEARCH("0603",C3))),"0.0028",IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%" ,C3)),ISNUMBER(SEARCH("X7R",C3)),ISNUMBER(SEARCH("0805",C3))),"0.0049",IF(AND(ISNUMBER(SEARCH("CAP", C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("X7R",C3)),ISNUMBER(SEARCH("1206",C3))),"0.0077",IF( AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("NP0",C3)),ISNUMBER(SEARCH( "0402",C3))),"0.0027",IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("C 0G",C3)),ISNUMBER(SEARCH("0402",C3))),"0.0027",IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10 %",C3)),ISNUMBER(SEARCH("NP0",C3)),ISNUMBER(SEARCH("0603",C3))),"0.0051",IF(AND(ISNUMBER(SEARCH("CAP ",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("C0G",C3)),ISNUMBER(SEARCH("0603",C3))),"0.0051",IF (AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("NP0",C3)),ISNUMBER(SEARC H("0805",C3))),"0.0046",IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH( "C0G",C3)),ISNUMBER(SEARCH("0805",C3))),"0.0046",IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH(" 10%",C3)),ISNUMBER(SEARCH("NP0",C3)),ISNUMBER(SEARCH("1206",C3))),"0.0103",IF(AND(ISNUMBER(SEARCH("C AP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("C0G",C3)),ISNUMBER(SEARCH("1206",C3))),"0.0103", ""))))))))))))))))
it is basically connected IF from several lines into one:
0402 SMD res:
=IF(AND(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("0402",C3))),"0.0005"," ")
0603 SMD res:
=IF(AND(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("0603",C3))),"0.0005"," ")
0805 SMD res:
=IF(AND(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("0805",C3))),"0.0009"," ")
1206 SMD res:
=IF(AND(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("1206",C3))),"0.0014"," ")
0402 SMD X7R cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("X7R",C3)),ISNUMBER(SE ARCH("0402",C3))),"0.0018","")
0603 SMD X7R cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("X7R",C3)),ISNUMBER(SE ARCH("0603",C3))),"0.0028","")
0805 SMD X7R cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("X7R",C3)),ISNUMBER(SE ARCH("0805",C3))),"0.0049","")
1206 SMD X7R cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("X7R",C3)),ISNUMBER(SE ARCH("1206",C3))),"0.0077","")
0402 SMD NP0 cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("NP0",C3)),ISNUMBER(SEA RCH("0402",C3))),"0.0027","")
0402 SMD C0G cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("C0G",C3)),ISNUMBER(SEA RCH("0402",C3))),"0.0027","")
0603 SMD NP0 cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("NP0",C3)),ISNUMBER(SE ARCH("0603",C3))),"0.0051","")
0603 SMD C0G cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("C0G",C3)),ISNUMBER(SEA RCH("0603",C3))),"0.0051","")
0805 SMD NP0 cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("NP0",C3)),ISNUMBER(SE ARCH("0805",C3))),"0.0046","")
0805 SMD C0G cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("C0G",C3)),ISNUMBER(SEA RCH("0805",C3))),"0.0046","")
1206 SMD NP0 cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("NP0",C3)),ISNUMBER(SE ARCH("1206",C3))),"0.0103","")
1206 SMD C0G cap:
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("5%",C3)),ISNUMBER(SEARCH("C0G",C3)),ISNUMBER(SEA RCH("1206",C3))),"0.0103","")
it would perfectly work IF my excel 2010 would alow to use so many IF statements... is there any way to put them into one formula?
This type of problem is notoriously difficult to solve with either formula or VBa.
There are almost bound to be situations not foreseen in the given criteria.
I would create a column that resolves the "Resistor" scenario and another for "Capacitor" then combine them.
That way it is easier to see where any non-conforming results might occur, and hence add to the formula to cope with them.
2003 will not allow me to combine the formulae I have here, but if you really need to use only one column you could try using nested IFERROR() statements
e.g.
=IFERROR(IFERROR("Resistor Formula","Capacitor Formula"),"")
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
I just solved my question with many IF statments. Somehow I had a file of 2003 excel that alows only 7 (or something) IF statments in one cell, while Excel 2010 has 64 and that is enough for me at the moment. I just put everything under many IF and believe me or not - it works perfectly.
Basically what Im trying to do (I think I should have said that in the beginning, sorry for that
since I will run out of 64 IF statments anyway - maybe there is a way to do the same with VBA.
If someone has an idea how to manage same thing under VBA - you're welcome to do it and to help me creating this fine tool that will help in my work a lot
Thanks everyone for ideas !
Here is how formula looks like:
IF(AND(ISNUMBER(SEARCH("RES",A1)),ISNUMBER(SEARCH("1%",A1)),ISNUMBER(SEARCH("0402",A1))),"0.0005",IF (AND(ISNUMBER(SEARCH("RES",A1)),ISNUMBER(SEARCH("1%",A1)),ISNUMBER(SEARCH("0603",A1))),"0.0005",IF(A ND(ISNUMBER(SEARCH("RES",A1)),ISNUMBER(SEARCH("1%",A1)),ISNUMBER(SEARCH("0805",A1))),"0.0009",IF(AND (ISNUMBER(SEARCH("RES",A1)),ISNUMBER(SEARCH("1%",A1)),ISNUMBER(SEARCH("1206",A1))),"0.0014",IF(AND(I SNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER(SEARCH("50V ",A1)),ISNUMBER(SEARCH("0402",A1))),"0.0018",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("10%" ,A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER(SEARCH("25V",A1)),ISNUMBER(SEARCH("0402",A1))),"0.0018",IF (AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER(SEARC H("16V",A1)),ISNUMBER(SEARCH("0402",A1))),"0.0018",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH ("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER(SEARCH("50V",A1)),ISNUMBER(SEARCH("0603",A1))),"0.00 28",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER (SEARCH("25V",A1)),ISNUMBER(SEARCH("0603",A1))),"0.0028",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER( SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER(SEARCH("16V",A1)),ISNUMBER(SEARCH("0603",A1))) ,"0.0028",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),IS NUMBER(SEARCH("50V",A1)),ISNUMBER(SEARCH("0805",A1))),"0.0049",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISN UMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER(SEARCH("25V",A1)),ISNUMBER(SEARCH("0805" ,A1))),"0.0049",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A 1)),ISNUMBER(SEARCH("16V",A1)),ISNUMBER(SEARCH("0805",A1))),"0.0049",IF(AND(ISNUMBER(SEARCH("CAP",A1 )),ISNUMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER(SEARCH("50V",A1)),ISNUMBER(SEARCH( "1206",A1))),"0.0077",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH(" X7R",A1)),ISNUMBER(SEARCH("25V",A1)),ISNUMBER(SEARCH("1206",A1))),"0.0077",IF(AND(ISNUMBER(SEARCH("C AP",A1)),ISNUMBER(SEARCH("10%",A1)),ISNUMBER(SEARCH("X7R",A1)),ISNUMBER(SEARCH("16V",A1)),ISNUMBER(S EARCH("1206",A1))),"0.0077",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEA RCH("NP0",A1)),ISNUMBER(SEARCH("50V",A1)),ISNUMBER(SEARCH("0402",A1))),"0.0027",IF(AND(ISNUMBER(SEAR CH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("NP0",A1)),ISNUMBER(SEARCH("25V",A1)),ISNUMB ER(SEARCH("0402",A1))),"0.0027",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER (SEARCH("NP0",A1)),ISNUMBER(SEARCH("16V",A1)),ISNUMBER(SEARCH("0402",A1))),"0.0027",IF(AND(ISNUMBER( SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G",A1)),ISNUMBER(SEARCH("50V",A1)),IS NUMBER(SEARCH("0402",A1))),"0.0027",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNU MBER(SEARCH("C0G",A1)),ISNUMBER(SEARCH("25V",A1)),ISNUMBER(SEARCH("0402",A1))),"0.0027",IF(AND(ISNUM BER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G",A1)),ISNUMBER(SEARCH("16V",A1) ),ISNUMBER(SEARCH("0402",A1))),"0.0027",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)), ISNUMBER(SEARCH("NP0",A1)),ISNUMBER(SEARCH("50V",A1)),ISNUMBER(SEARCH("0603",A1))),"0.0051",IF(AND(I SNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("NP0",A1)),ISNUMBER(SEARCH("25V" ,A1)),ISNUMBER(SEARCH("0603",A1))),"0.0051",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A 1)),ISNUMBER(SEARCH("NP0",A1)),ISNUMBER(SEARCH("16V",A1)),ISNUMBER(SEARCH("0603",A1))),"0.0051",IF(A ND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G",A1)),ISNUMBER(SEARCH(" 50V",A1)),ISNUMBER(SEARCH("0603",A1))),"0.0051",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5 %",A1)),ISNUMBER(SEARCH("C0G",A1)),ISNUMBER(SEARCH("25V",A1)),ISNUMBER(SEARCH("0603",A1))),"0.0051", IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G",A1)),ISNUMBER(SEAR CH("16V",A1)),ISNUMBER(SEARCH("0603",A1))),"0.0051",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARC H("5%",A1)),ISNUMBER(SEARCH("NP0",A1)),ISNUMBER(SEARCH("50V",A1)),ISNUMBER(SEARCH("0805",A1))),"0.00 46",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("NP0",A1)),ISNUMBER( SEARCH("25V",A1)),ISNUMBER(SEARCH("0805",A1))),"0.0046",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(S EARCH("5%",A1)),ISNUMBER(SEARCH("NP0",A1)),ISNUMBER(SEARCH("16V",A1)),ISNUMBER(SEARCH("0805",A1)))," 0.0046",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G",A1)),ISNUM BER(SEARCH("50V",A1)),ISNUMBER(SEARCH("0805",A1))),"0.0046",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMB ER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G",A1)),ISNUMBER(SEARCH("25V",A1)),ISNUMBER(SEARCH("0805",A1) )),"0.0046",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G",A1)),I SNUMBER(SEARCH("16V",A1)),ISNUMBER(SEARCH("0805",A1))),"0.0046",IF(AND(ISNUMBER(SEARCH("CAP",A1)),IS NUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("NP0",A1)),ISNUMBER(SEARCH("50V",A1)),ISNUMBER(SEARCH("1206" ,A1))),"0.0103",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("NP0",A1 )),ISNUMBER(SEARCH("25V",A1)),ISNUMBER(SEARCH("1206",A1))),"0.0103",IF(AND(ISNUMBER(SEARCH("CAP",A1) ),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("NP0",A1)),ISNUMBER(SEARCH("16V",A1)),ISNUMBER(SEARCH("1 206",A1))),"0.0103",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G ",A1)),ISNUMBER(SEARCH("50V",A1)),ISNUMBER(SEARCH("1206",A1))),"0.0103",IF(AND(ISNUMBER(SEARCH("CAP" ,A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH("C0G",A1)),ISNUMBER(SEARCH("25V",A1)),ISNUMBER(SEARC H("1206",A1))),"0.0103",IF(AND(ISNUMBER(SEARCH("CAP",A1)),ISNUMBER(SEARCH("5%",A1)),ISNUMBER(SEARCH( "C0G",A1)),ISNUMBER(SEARCH("16V",A1)),ISNUMBER(SEARCH("1206",A1))),"0.0103","")))))))))))))))))))))) ))))))))))))))))))
Hi everyone,
Even I have solved my question with many IFs, I would like to ask if somebody knows how could I put these IFs into VB.
Lets say I would like to have two questions as macros :
=IF(AND(ISNUMBER(SEARCH("RES",C3)),ISNUMBER(SEARCH("1%",C3)),ISNUMBER(SEARCH("0603",C3))),"0.0005"," ")
=IF(AND(ISNUMBER(SEARCH("CAP",C3)),ISNUMBER(SEARCH("10%",C3)),ISNUMBER(SEARCH("X7R",C3)),ISNUMBER(SE ARCH("0402",C3))),"0.0018","")
maybe these could be added into macros in such way that I could easily add as many statments as I like and make this script as big as I want only by changing search parameters (as "res", "1%", "0603" and so on).
BR
I took a shot at giving you an easy way to expand this. First, you will need a "table" where you lay all this stuff out. The only requirement is that table have the "results" you want to give back in the final column. You can have as many columns to the left of that as you wish.
You can define the table as a named range like I did in the attached workbook, the sheet Lookup has a dynamic named range on it called MyTable, you can add as many rows/columns as you want and it will adjust itself. Don't use this sheet for anything else, keep it clean.
Next, here is the custom function I wrote to analyze your string and utilize your lookup table:
Option Explicit Function SUPERSEARCH(MyVal As Range, MyData As Range) As Variant 'Jerry Beaucaire 1/24/2012 'Search a string to find mutliple associated strings from a table Dim opt As Long, c As Long, v As Long, FoundIt As Boolean If MyData.Rows.Count < 3 Then SUPERSEARCH = "table not correct" Exit Function End If Set MyVal = MyVal.Cells(1, 1) 'in case multiple cells get fed in With MyData c = .Columns.Count For opt = 1 To .Rows.Count For v = 1 To c - 1 If Not IsEmpty(.Cells(opt, v)) Then If Not UCase(MyVal.Text) Like "*" & UCase(.Cells(opt, v).Text) & "*" Then Exit For End If If v = c - 1 Then SUPERSEARCH = .Cells(opt, c) Exit Function End If Next v Next opt SUPERSEARCH = "not found" End With End Function
Lastly, the function is used in a cell like a regular formula, it has two parameters, the first is the cell with the string being searched, the second parameter is the table of lookup values.
=SUPERSEARCH(A1, MyTable)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
JBeaucaire, that is totally amazing. And now I can make my work muuuuuuch faster.
Thanks a lot!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks