Multiple word search in Excel

July 13, 2012 at 04:33:42
Specs: Windows XP
In Excel 2003, I am looking to search a specific cell, and return a list of keywords. eg
Search Cell A1 for CAP and Ceramic
Value of A1: "CAP 4.7uF +/-10% 6.3V X5R 0603 (CERAMIC, MULTILAYER,SMD)"
And paste in A2 a result corresponding to that : "Capacitors, Discrete, Fixed, Ceramic"
This then gets used in a Vlookup to find a corresponding data.

HOWEVER!!
The equasion/macro/whatever needs to also search for RES (resistor) Diode .... and about 200 other key words...

I have very limited experience with macros.... so either post a link to a file, or explain it simply XD

At the moment i have an indirect Validation List system that requires the user to maunally go through and choose what values....

Ideally I want this automated... there are list that have neer 1000s of componets on them... and we ahve lots of these lists that all need their values calculated....

What do you suggest?
(I'll be back on monday 16th.....)


See More: Multiple word search in Excel

Report •


#1
July 13, 2012 at 06:21:40
re: "At the moment i have an indirect Validation List system that requires the user to maunally go through and choose what values...."

I'm confused. What is the Validation List used for?

How are you expecting the user to input the search terms e.g. cap & ceramic?

Perhaps if you posted a few examples of your data and the expected output based on those examples it might help.

Before posting example data, please click on the following line and read the instructions on how to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 16, 2012 at 02:00:06
User List							Type		Sub Type		(sub) Sub type	Price
RES 62ohm 1% 63mW MET 0603  (RES.CHIP    62o  1%  50V  0603)	Resistors		Surface Mount	Film (ceramic)	0.001614
RES 68ohm 1% 63mW MET 0603  (RES.CHIP    68o  1%  50V  0603)	Resistors		Surface Mount	Film (ceramic)	0.001614
RES 68ohm 2% 125mW MET 1206  (RES.CHIP 1206 68ohm    2%)	Resistors		Surface Mount	Film (ceramic)	0.001614
RESISTOR 51 Ohm 1% 50V 0402					Resistor		Surface Mount	Film (RL, RLR, RN, RNR, RM)	0.006763
RESISTOR NETWORK 741X083 51ohms				Resistor		Surface Mount	Network Film (RZ)	0.378625
RES 18ohm 1% 63mW MET 0603  (RES.CHIP    18o  1%  50V  0603)	Resistors		Surface Mount	Film (ceramic)	0.001614
RES 22ohm 1% 63mW MET 0603  (RES.CHIP    22o  1%  50V  0603)	Resistors		Surface Mount	Film (ceramic)	0.001614
CAP 47uF 20 10V TANTALUM CASE B  (ESR 70 mohm)		Capacitors	Tantalum		Solid, Elec, Tant 	0.016772
CAP 33uF 10 35V SOLID TANTALUM 7343				Capacitors	Tantalum		Solid, Elec, Tant 	0.016772
CAP 1uF 10 25V TANTALUM CASE A  (Case A 3.2x1.6x1.6mm, 8 Ohm ESR) Capacitors	Tantalum		Solid, Elec, Tant 	0.016772
CAP 4.7uF +/-10% 6.3V X5R 0603  (CERAMIC, MULTILAYER,SMD)	Capacitors	Ceramic		Chip, Ceramic	0.005303
CAP 47uF +/-20% 10V X5R 1206  (Multilayer Ceramic )		Capacitors	Ceramic		Chip, Ceramic	0.005303


Report •

#3
July 16, 2012 at 02:04:12
so the idea is to find the price list collum from the User list collum. But it needs to find 'key' data (Type, subtype and sub sub type) in the user list, and separate it from the junk. this then allows the key data to be used to search for the prices. the time consuming (and therefore expensive) part is the separating of the data into its key information.
These lists can be literally 1000s of componetents long...

Report •

Related Solutions

#4
July 16, 2012 at 07:10:28
2 issues:

1 - You didn't answer my question about the Data Validation list, so we don't know what your users are doing.

2 - You didn't use Row Number or Column letters so it's hard to tell which columns are which, especially since your data does not appear to line up correctly. If we start making assumptions about your data lay out, we run the risk of wasting time and effort working in the wrong solution.


Keep in mind that we can't see your spreadsheet from where we're sitting nor can we read minds.

You need to tell us what your users are currently doing, how it works, what the results are and what you are ultimately trying to accomplish.

Examples of specific input and the expected output from that input are a great help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 16, 2012 at 08:13:17
i want to replace the data validation list....
thats why i wasn't telling you much about it....
This is what happens at the moment:

1)the user pastes the 'user list' into the spreadsheet

2)using dropdown menu1 (data validation:list) selects 'Type' from:
Diodes
Transistors
Optics
Capacitors
Resistors
Gates
IC
RAM
ROM
Inductors
Motors
Relay
Switch
Connector
Misc.

3)using dropdown menu2 (data validation:indirect list) selects 'SubType' from (eg resistors):
Resistor, Composition
Resistor, Film, Insulated
Resistor, Film, RN
Resistor, Film, Chip
Resistor, Film
Resistor, Film, Power
Resistor, Film, network
Resistor, Wirewound, Accurate
Resistor, Wirewound, Power
Resistor, Wirewound, Power, Chassis Mounted
Resistor, Thermistor
Resistor, Wirewound, Variable
Resistor, Wirewound, Variable, Precision
Resistor, Wirewound, Variable, Semiprecision
Resistor, Wirewound, Variable, Power
Resistor, Nonwirewound, Variable
Resistor, Composition, Variable
Resistor, Nonwirewound, Variable, Precision
Resistor, Film, Variable

4)The spreadsheet then uses the sub type to search a second sheet and retrieve the price of that component.

I would like to automate this.....

This being the manual dropdowns. If there was a way of searching a cell in the 'user list' for key data/strings then using the found words to form a new string, it would be really useful. eg:
I)Search in cell A45 (who's value = CAP 5.6pF 0,25P 50V CER 0603 COND.CHIP 0603 5,6pF 0,25PF 50V NP0) for the terms:
cap
res
diode
reg
etc...
II) then when 'cap' returns true, search for :
Tant
Cer
III) then when 'cer' returns true:
chip
wire
IV) then when that retuns true, output 'Capacitors, Ceramic Chip'
V) this then gets put through the existing VLOOKUP search


Report •

#6
July 16, 2012 at 08:51:56
What will the search be based on? In other words, how are you expecting the user to enter the keywords "cap, cer, chip"?

Are you also expecting the user to "remember" all of the keywords? It seems to me that the Validation Lists allow anyone to search for what they are looking for since (I assume) all of the keywords are listed.

How will you handle updates? Changing the Validation Lists seems like an easy way to change the keywords without the users needing to know anything.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
July 16, 2012 at 09:28:39
here is an example of what will be pasted into the spreadsheet under 'user list' :

INDICATOR LED HIGH EFFICIECY RED SOT-23

Visible Light LED 0603 (HYPER RED, SMD, 1.6 X 0.8 X 1.1(ht))

Visible Light LED 0603 (MEGA GREEN 0603(1608))

Optocoupler PHOTODARLINGTON SOP-4L (SOP-4L )

Relay 2 12V 56 SERIES RELAY (12V 12A PCB RELAY 2POLE DPDT THOUGH HOLE CONTACTS)

Relay 1 12V SPDT 32 SERIES (12V 6A PCB RELAY 1 POLE CO SPDT THOUGH HOLE CONTACT

Res array 33ohm 5% 62mW ISOLATED 3.2 X 1.6 QUAD (4 x 33R)

Res array 470kohm 5% _W ISOLATED 3.2X1.6

RES 100ohm 1% 100mW MET 0805 (RES.CHIP 0805 100 ohm 1%)

RES 130ohm 1% 63mW MET 0603 (0603)

RES 150ohm 1% 63mW MET 0603 (RES.CHIP 150o 1% 50V 0603)

RES 15ohm 1% 63mW MET 0603 (RES.CHIP 15o 1% 50V 0603)

RES 160kohm 1% 100mW CERAMIC 0603 (0603)

this list comes from some software, once the circuit board is populated/designed. thus it is automaticaly generated, different components have different amounts of data. Using the Data validation lists takes too much time, something that we don't have.


Report •

#8
July 16, 2012 at 09:46:59
You still have not answered this question:

"What will the search be based on? In other words, how are you expecting the user to enter the keywords "cap, cer, chip"?"

You keep saying that you want to search the "user list" based on multiple keywords, but you haven't told us how the keywords will become known to whatever search mechanism is employed.

They have to be entered, stored, chosen, etc.

Right now the input is coming from the Data Validation lists, right? You want to eliminate the Data Validation lists, right?

So what will replace the Data Validation lists so that the search mechanism knows what strings to search for?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
July 16, 2012 at 09:48:55
"this list comes from some software" ... "thus it is automaticaly generated"

Report •

#10
July 16, 2012 at 09:52:59
All i'm looking for is a system to replace the drop down menu system. the lists used currently as Data validation lists can/will remain, as this is what the vlookup is based apon. however rather than having to personally choose each level by hand/mouse the macro/code/function that i'm looking for will do it instead.

Report •

#11
July 16, 2012 at 10:22:27
I don't know if it's me or you, but I'll try one more time.

In Reponse # 7, you posted what you are calling the "user list". above the phrases "this list comes from some software" and "thus it is automaticaly generated"

That is not what I am asking about.

Correct me where I'm wrong here:

1 - You want to search that "user list" for specific combinations of keywords, right? e.g "cap, cer, chip"

2 - Currently, those specific combinations of keywords are coming from the Dependent Drop Down Lists, right?

3 - You want to eliminate the user task of choosing the search String from the Dependent Drop Down Lists, right?

If the use of Dependent Drop Down Lists is eliminated where will the seach criteria come from?

Will the user yell "cap, cer, chip" at his monitor? Somehow, the search criteria has to be given to Excel so that it can search the "user list" for the search terms, right?

What am I missing here?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
July 17, 2012 at 01:18:29
1) yes
2) yes
3) yes
the use of the drop downs will be eliminated, the lists they were based on can remain.
the fact that is that I have no idea what format the keywords list needs to be in. It can be on another sheet, a txt file, what ever is required. the location of such a list is unimportant. if its currently in the wrong place/format, i'll move it.

Report •

#13
July 17, 2012 at 04:39:24
Well, perhaps we're closer but we're not there yet.

Let me try it this way.

I am a user. I need the price of this item:

Res array 33ohm 5% 62mW ISOLATED 3.2 X 1.6 QUAD (4 x 33R)

How am I going to tell the system that I want the price of that specific item?

Even if there is a list keywords someplace, I need a way the choose those keywords don't I?

That is the main part that I am still not getting. You want to eliminate the current method that the users use to choose the keywords - the drop downs - but you haven't provided any other means for them to input the keywords.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
July 17, 2012 at 04:42:26
Well, perhaps we're closer but we're not there yet.

Let me try it this way.

I am a user. I need the price of this item:

Res array 33ohm 5% 62mW ISOLATED 3.2 X 1.6 QUAD (4 x 33R)

How am I going to tell the system that I want the price of that specific item?

Even if there is a list of keywords someplace, I need a way the choose those keywords don't I?

That is the main part that I am still not getting. You want to eliminate the current method that the users use to choose the keywords - the drop downs - but you haven't provided any other means for them to input the keywords.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
July 17, 2012 at 05:42:08
you paste the data "Res array 33ohm 5% 62mW ISOLATED 3.2 X 1.6 QUAD (4 x 33R)" in to a cell, eg A856. Then Automatically the spreadsheet searches that box (A856) for the keywords, defined by the 'programmer'
The user doesn't input keywords, just the description that comes from another piece of software in the form of a list

Report •

#16
July 17, 2012 at 05:48:56
Is it even possible?

Report •

#17
July 17, 2012 at 06:05:40
Basically, the person using this data base will enter :
'Res array 33ohm 5% 62mW ISOLATED 3.2 X 1.6 QUAD (4 x 33R)' into the first cell.
They do not want to spend the time filling in the following columns when the information is in the data above but just not in full english.
We are looking for a formula that can be entered into the corresponding cells that will recognise the information and automatically enter it into the correct columns along side it.
For instance,
'Res array 33ohm 5% 62mW ISOLATED 3.2 X 1.6 QUAD (4 x 33R)'
has:
Res, implying it is a resistor
Array, meaning it is a networked chip.
We are wanting the next two cells in the row to automatically pick this up andlook like:
Res array 33ohm 5% 62mW ISOLATED 3.2 X 1.6 QUAD (4 x 33R)¦ Resistor¦Networked Array

We would like it to also search for a variety of other words/phrases at the same time to fill in the remaining columns.


Report •

#18
July 17, 2012 at 06:10:20
Finally! That is the piece of information I have been looking for all along.

Is it possible? I don't know yet, but at least I know what I am working with.

Now, there is something else I could use from you to make sure that we are working with the same data.

Let's say I start with the list of items in Response #7.

Please provide subset of the list of search keywords that would be used, ensuring that the list contains keywords that will actually find a couple of the items in the list.

To be sure that there is no misunderstanding, please indicate something like "These 3 keywords should return this item"

I want to make sure that I have data that will actually return positive results so that anything I write actually returns those results. Otherwise, I'm back to guessing what I think the answer should be and that's just a further waste of our time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
July 17, 2012 at 06:31:08
Data (pasted by user):
CAPACITOR FIXED 100nF 100V SMT
CAP 470uF 20% 16V NACZ 8X10,5
CAP 470uF 20% 63V WET Al 16X16.5MM SMT (SMT)
CAP 1.5pF 0,1PF 250V ATC 0603 (Ultra-Low ESR, High Q Microwave Capacitors)
CAP 3.9pF 0,1PF 250V ATC 0603 (Ultra-Low ESR, High Q Microwave Capacitors)
CAP 6.8pF 0,1PF 250V ATC 0603 (Ultra-Low ESR, High Q Microwave Capacitors)
CAP 5.1pF +/-0,05PF 250V NP0 0603 (Ultra-Low ESR, High Q Microwave Capacitors)
CAP 7.5pF +/-0.25PF 250V Ultra-Low ESR, High Q (NPO) 0603
CAP 47uF 20 10V TANTALUM CASE B (ESR 70 mohm)
CAP 33uF 10 35V SOLID TANTALUM 7343
CAP 1uF 10 25V TANTALUM CASE A (Case A 3.2x1.6x1.6mm, 8 Ohm ESR)
CAP 100uF 10% 10V SOLID TANTALUM 6032 (CASE C 6032)
CAP 47uF 10% 10V TANTALUM 3528 (CASE B 3528-21)
CAP 10uF 10 16V DRY Ta 1206 (Case A)
IND 56nH 2% 500mA 0805 (INDUTTORE 56 nH 2% 0805)
IND 33nH 2% 500mA 0805
IND 27nH 2% 500mA 0805 (INDUTTORE 27 nH 2% 0805)
IND 180nH 10% 400mA 0805
RES 82ohm 1% 63mW MET 0603 (RES.CHIP 82o 1% 50V 0603)
DIODE SCHOTTKY SOT-23 PLASTIC
Diode sig _V 300mA 200mW SOT323 (2 SCHOTTKY DIODES. COMMON ANODE)


Report •

#20
July 17, 2012 at 06:38:51
search for:
"cap "-> "cer" or "tant"=>"Capacitor, Ceramic" or "Capacitor Tantalum" respectively
"res " -> "array" or ""=> "Resistor, Network array" or "Resistor, Film" respectively
"Ind" =>Inductor
"diode"-> "schottky" or "zener" =>"diode, Schottky" or "diode, zener" respectively

Report •

#21
July 17, 2012 at 06:40:22
oh and if its not a "tant" capacitor, then its ceramic

Report •

#22
July 17, 2012 at 07:11:39
So if I searched for "cap" and "tant" it should find all of these lines, right?

CAP 47uF 20 10V TANTALUM CASE B (ESR 70 mohm)
CAP 33uF 10 35V SOLID TANTALUM 7343
CAP 1uF 10 25V TANTALUM CASE A (Case A 3.2x1.6x1.6mm, 8 Ohm ESR)
CAP 100uF 10% 10V SOLID TANTALUM 6032 (CASE C 6032)
CAP 47uF 10% 10V TANTALUM 3528 (CASE B 3528-21)

When it finds these lines, then what?

It looks like you want an output of "Capacitor Tantalum", right?

So you would want Capacitor Tantalum listed 5 times someplace. Is that correct?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#23
July 17, 2012 at 07:30:47
nope, you put paste "CAP 47uF 20 10V TANTALUM CASE B (ESR 70 mohm)"
in cell A1
In cell B1 it automatically searches A1 for all key words, to which "cap " and "tant" return positive, enabling it to show "Capacitor Tantalum"
Then in A2 you paste another component, etc down the list

Report •

#24
July 17, 2012 at 07:34:01
as a wordy equation:
does A1 contain "res " -> No
then does A1 contain "cap "-> yes
thus "does A1 contain "tant" -> yes
thus Output in B1 "Capacitor Tantalum"


Report •

#25
July 17, 2012 at 07:43:13
re reading what you've put, Cap and Tant should be found in those lines, not used to find those lines. and yes, Capacitor Tantalumwould be listed 5 times next to the corresponing cells

Report •

#26
July 17, 2012 at 08:08:28
This is not going to be easy.

It is going to require a macro with multiple search loops.

I'll give it a shot, but since this is all volunteer work, I can't dedicate a huge amount of time to it.

I'll be in touch when I have something (or nothing).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#27
July 17, 2012 at 08:15:00
thanks, your help is greatly appreciated

Report •

#28
July 20, 2012 at 02:30:57
Here is what i have so far..... with the list of keywords on sheet: "list" Cell "e6" downwards, and the Data pasted in by the user on Sheet:"PCBA" Cell: "B13" downwards. How can i make the it search for the exact text? aka case sensitive?
Sub Test2()
Dim searchterm
Dim checkvalue As Integer
Dim Answerlocation

Sheets("List").Select
Range("E3").Select
searchterm = ActiveCell.Address
 
Sheets("PCBA").Select
Range("D13").Select
Answerlocation = ActiveCell.Address

Do
 Sheets("List").Select
 Range("E3").Select
 searchterm = ActiveCell.Address
 Do
  Sheets("PCBA").Select
  Range(Answerlocation).Select
  checkvalue = 0
  checkvalue = (InStr(1, (ActiveCell.Offset(0, -2)), Sheets("List").Range(searchterm), vbTextCompare))
  If (checkvalue > 0) Then
   Sheets("PCBA").Select
   Range(Answerlocation).Select
   ActiveCell.FormulaR1C1 = Sheets("List").Range(searchterm).Offset(0, -1)
   Exit Do
  Else
   Sheets("list").Select
   Range(searchterm).Offset(1, 0).Select
   searchterm = ActiveCell.Address
  End If
  Sheets("List").Select
  Range(searchterm).Select
  Loop Until IsEmpty(ActiveCell.Offset(1, 0))
 Sheets("PCBA").Select
 Range(Answerlocation).Select
 Answerlocation = ActiveCell.Offset(1, 0).Address
 Range(Answerlocation).Select
 Loop Until IsEmpty(Range(Answerlocation).Offset(0, -2))
End Sub





    

   




Report •

#29
July 20, 2012 at 05:45:55
Well, just looking at your code, there are some things I see that can help the code be more efficient and some things I see that I don't understand.

First, rarely, if ever, do you have to Select an object in VBA to perform an action on it. For example, this type of code:

   If (checkvalue > 0) Then
     Sheets("PCBA").Select
     Range(Answerlocation).Select
      ActiveCell.FormulaR1C1 = Sheets("List").Range(searchterm).Offset(0, -1)

can be written as:

   If (checkvalue > 0) Then
      Sheets("PCBA").Range(Answerlocation).FormulaR1C1 = _
        Sheets("List").Range(searchterm).Offset(0, -1)

In other words, you can work directly with the object without selecting it.

This will not only make your code more efficient, but also easier to read and follow.

Second, not only can this code be shortened from:

Sheets("List").Select
Range("E3").Select
searchterm = ActiveCell.Address

to:

searchterm = Sheets("List").Range("E3").Address

but I don't understand why you are using VBA to grab the address of E3 when you already know what it is. Why not just use this?

searchterm = "$E$3"

Finally, assuming that I can use the table in Response #19 as the user data, please post a short list of the keywords used in the "list" sheet so that I can see exactly what your code does with your exact data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#30
July 24, 2012 at 02:22:50
searchterm = "$E$3" means E3 on the active sheet right? how do you make it only go to other sheets?

Report •

Ask Question