Search and Count

[sndfx69]

Hi All,

I would like a macro that would search a range of cells for a specific word and also count how many times the word has been used in the range, because the word is entered in the range more than once.

Thank you in advance for your support.

 

[zaHawk]

There are hundreds of these.. so when I type in the wild card word.. I need something to gather the relevant lines

S.S.316 BALL VALVE 2 WAYS 1.1/4"
S.S.316 BALL VALVE 2 WAYS 2"
S.S.304 HOSE CLAMPS 11-17
S.S.304 HOSE CLAMPS 13-20
DIN 2982 WELD NIPP.S.S.316 1/2"
DIN 2982 WELD NIPP.S.S.316 1/4"
DIN 2986 FULL SOCKET S.S. 316 3/8"
DIN 2986 FULL SOCKET S.S. 316 1"
DIN 2986 FULL SOCK.S.S.316 1"1/2
DIN 2986 FULL SOCK.S.S.316 1"1/4
DIN 2986 FULL SOCKET S.S. 316 2"
DIN 2986 FULL SOCK.S.S.316 2"1/2
HALF SOCKETS S.S.316 1/2"
HALF SOCKETS S.S.316 1/4"
S.S.316 UNION CONICAL SEAT 1/2"
S.S.316 UNION CONICAL SEAT 1/4"

 

[T. Valko]

There are hundreds of these.. so when I type in the wild card word.. I need something to gather the relevant lines

S.S.316 BALL VALVE 2 WAYS 1.1/4"
S.S.316 BALL VALVE 2 WAYS 2"
S.S.304 HOSE CLAMPS 11-17
S.S.304 HOSE CLAMPS 13-20
DIN 2982 WELD NIPP.S.S.316 1/2"
DIN 2982 WELD NIPP.S.S.316 1/4"
DIN 2986 FULL SOCKET S.S. 316 3/8"
DIN 2986 FULL SOCKET S.S. 316 1"
DIN 2986 FULL SOCK.S.S.316 1"1/2
DIN 2986 FULL SOCK.S.S.316 1"1/4
DIN 2986 FULL SOCKET S.S. 316 2"
DIN 2986 FULL SOCK.S.S.316 2"1/2
HALF SOCKETS S.S.316 1/2"
HALF SOCKETS S.S.316 1/4"
S.S.316 UNION CONICAL SEAT 1/2"
S.S.316 UNION CONICAL SEAT 1/4"

Ok, if you were going to seach that data for some criteria what would it be and what result do get that is not correct?

 

[zaHawk]

Ok, if you were going to seach that data for some criteria what would it be and what result do get that is not correct?

If I type in HOSE (upper or lower caps).. in the query cell.. I only want the following result to appear

S.S.304 HOSE CLAMPS 11-17
S.S.304 HOSE CLAMPS 13-20

I could use this in a filter list (if cell to right gets value 1 - for example - then select 1 to see them)
.. or if you can suggest something better (vlookup?)

 

[T. Valko]

If I type in HOSE (upper or lower caps).. in the query cell.. I only want the following result to appear

S.S.304 HOSE CLAMPS 11-17
S.S.304 HOSE CLAMPS 13-20

I could use this in a filter list (if cell to right gets value 1 - for example - then select 1 to see them)
.. or if you can suggest something better (vlookup?)

Book1
	A	B	C	D	E
2	S.S.316 BALL VALVE 2 WAYS 1.1/4"	0		Search For	hose
3	S.S.316 BALL VALVE 2 WAYS 2"	0
4	S.S.304 HOSE CLAMPS 11-17	1
5	S.S.304 HOSE CLAMPS 13-20	1
6	DIN 2982 WELD NIPP.S.S.316 1/2"	0
7	DIN 2982 WELD NIPP.S.S.316 1/4"	0
8	DIN 2986 FULL SOCKET S.S. 316 3/8"	0
9	DIN 2986 FULL SOCKET S.S. 316 1"	0
10	DIN 2986 FULL SOCK.S.S.316 1"1/2	0
11	DIN 2986 FULL SOCK.S.S.316 1"1/4	0
12	DIN 2986 FULL SOCKET S.S. 316 2"	0
13	DIN 2986 FULL SOCK.S.S.316 2"1/2	0
14	HALF SOCKETS S.S.316 1/2"	0
15	HALF SOCKETS S.S.316 1/4"	0
16	S.S.316 UNION CONICAL SEAT 1/2"	0
17	S.S.316 UNION CONICAL SEAT 1/4"	0
Sheet1

Formula entered in B2 and copied down:

=COUNTIF(A2,"*"&E$2&"*")

 

[zaHawk]

Thanks.. with filtering.. that does the job just fine..
What I was missing was the & sign

 

[T. Valko]

Thanks.. with filtering.. that does the job just fine..
What I was missing was the & sign

Good deal. Thanks for the feedback!