Tricky ... Pullout related rows that match a single word using Vlookup

[YJL]

Dear Gurus . ...

I have a worksheet with 5 columns and 400 rows. All contents ar
sentences.
I use Vlookup but it only can show result that matches the inpu
exactly.
How to ask vlookup to retrieve all the sentences that match my singl
input?
And how to ask vlookup shows multiple searching result? (It stops afte
getting the first from top)

Please help. Thanks in advance.

YJ

 

[Biff]

Provide some examples. Be very specific!

Biff

 

[YJL]

Here is my example.

I want to search "abc"

Data

A B C D E
1 abcde fghij klmno pqrst uvwxy

2 fghij klmno pqrst uvwxy abcde

3 klmno pqrst uvwxy pqrst uvwxy

4 edabc fghij klmno pqrst uvwxy

 

[Biff]

Hi!

Ok, I'm not sure what you're trying to do.

Vlookup can only search the leftmost column of a range and return data from
the nth column to the right.

So, Vlookup will not do what I think you want.

Have you tried filtering?

Biff

 

[YJL]

Thanks for your reply.

Vlookup can only search the leftmost column of a range--> In that case
if i only want to search the leftmost column, can I do this?

Example.

I want to search "abc"

Data

A B C D E
1 abcde fghij klmno pqrst uvwxy

2 fghij klmno pqrst uvwxy abcde

3 klmno pqrst uvwxy pqrst uvwxy

4 edabc fghij klmno pqrst uvwxy

 

[Biff]

Hi!

I think you'd be better off using a filter.

That being said, here's a formula that will extract all of the entries from
column A that contain the substring "abc".

Based on your posted sample of data in A1:A4.

Array entered using the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(A$1:A$4,"*abc*")>=ROWS(A$1:A1),INDEX(A$1:A$4,SMALL(IF(ISNUMBER(SEARCH("abc",A$1:A$4)),(ROW(A$1:A$4)-1)+ROW(A$1)),ROWS(A$1:A1))),"")

Copy down until you get blanks.

Will return:

abcde
edabc

Biff

 

[Biff]

Slight correction:

=IF(COUNTIF(A$1:A$4,"*abc*")>=ROWS(A$1:A1),INDEX(A$1:A$4,SMALL(IF(ISNUMBER(SEARCH("abc",A$1:A$4)),(ROW(A$1:A$4)-ROW(A$1))+1),ROWS(A$1:A1))),"")

Biff

 

[YJL]

Sorry Biff,Could you tell me how to input the key combo
enter+shift+ctrl?? and where should i put the formula you mentioned?

YJL

 

[Biff]

Hi!

where should i put the formula you mentioned?

Put it where you wanted to put the Vlookup formula. Since you're returning
possibly more than a single result you have to drag copy down to more cells
in order to extract all the data that meets the criteria.

tell me how to input the key combo enter+shift+ctrl??

Type the formula. Instead of just hitting the ENTER key, hold down the CTRL
key and the SHIFT key then hit ENTER.

Biff

 

[YJL]

Thanks Biff, I got part of it done. However, I couldnt get multipl
search result as you mentioned. I couldnt find what I have done wrong
On top of that how to make it searches what has been input in a cell.
have attached my file for your reference. COuld you kindly look it. I
is almost done. Thanks in advance.

YJ

+-------------------------------------------------------------------
|Filename: help.zip
|Download: http://www.excelforum.com/attachment.php?postid=4035
+-------------------------------------------------------------------

 

[Biff]

Hi!

Here's your file:

http://s45.yousendit.com/d.aspx?id=0HDPV352RW0WT2HYG420LQN5T2

Biff

 

[Biff]

Change the formula in B20:

=IF(A$20="","","Row#"&MATCH(C20,A$1:A$4,0))

Change to:

=IF(C20="","","Row#"&MATCH(C20,A$1:A$4,0))

Copy down as needed.

Biff

 

[YJL]

Thanks Biff. I have the filtering run perfectly.

Now, I insert a textbox and two common button-"search" and "clear".
I want user to key-in their text in the text box and press "search"
command button; or press "clear" command button to clear the input
text.

I did it in very primitive way. I used macro to record my routine. 1.
copy text in the textbox. 2.paste it to filtering sheet.

And it doesnt work out as i wish. Can it be done with macro??

Thanks

YJL

 

[Biff]

And it doesnt work out as i wish. Can it be done with macro??

I'm sure that it can but I don't know enough about VBA to help.

Try posting in the Programming forum.

Biff

 

[Peo Sjoblom]

Why not use autofilter with an input box and filter kinda like

Sub Test_Me()
Application.ScreenUpdating = False
UserVal = Application.InputBox("Enter Search String")
If UserVal = False Then
Exit Sub
Else
Selection.AutoFilter Field:=1, Criteria1:="*" & UserVal & "*"
End If
Application.ScreenUpdating = True
End Sub


will filter the first autofiltered column where you put in the search
criteria in an input box
You can attach the macro to a button and have another button to reset the
autofilter
It doesn't make sense to both have very complicated formulas then using
macros as well when it can be done
with a simple autofilter and macro, the following will reset the autofilter
and select cell A1

Sub Reset_Filter()
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.FilterMode Then
On Error Resume Next
sh.ShowAllData
End If
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub

--
Regards,

Peo Sjoblom

(No private emails please)

 

[YJL]

Thanks Biff and Peo Sjoblom for a great suggestion. But i never use vba
to create any kind of input boxes. Could you kindly explain to me where
should i get started? Where should i put your code into? Please advise.
Thanks.

YJL

 

[Max]

.. where should i get started? Where should i put your code into? ...

Just some implementation assist to illustrate how to get Peo's subs
operational ..

Sample implementation at:
http://cjoint.com/?lohmdZtMUz
Pullout_related_rows_that_match_a_single_word_using_Vlookup_YJL_misc.xls
(Note: Save the file to folder and then open from there. It may not work
properly if opened within the browser window.)

In Excel,
Press Alt+F11 to go to VBE
Click Insert > Module
Paste Peo's 2 subs: Test_Me & Reset_Filter (below)
into the code window on the right

Press Alt+Q to get back to Excel
In Excel, draw a button from the Forms Toolbar on the sheet
(If reqd, activate the forms toolbar via View > Toolbars > Forms)

In the Assign Macro dialog, select Test_Me, click OK
Change the button caption to say: Search

Draw another button, select Reset_Filter, click OK (caption change to:
Clear)

(You can always right-click on the button to get back to the dialog, if
required)

Move and position the 2 buttons within say, row1 (expand the row height
first)
Select A2, then click Window > Freeze pane
This will freeze the pane so that row1 always remain in view when you scroll
down

Now test it out ...
Click Search button > Type in the inputbox, say: YJL > OK
The filtered results will show in the sheet
Click Clear button > The whole table will be re-displayed in the sheet

'-----------
Sub Test_Me()
Application.ScreenUpdating = False
UserVal = Application.InputBox("Enter Search String")
If UserVal = False Then
Exit Sub
Else
Selection.AutoFilter Field:=1, Criteria1:="*" & UserVal & "*"
End If
Application.ScreenUpdating = True
End Sub
'---------

'----------
Sub Reset_Filter()
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.FilterMode Then
On Error Resume Next
sh.ShowAllData
End If
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub
'-----------

 

[YJL]

Thanks Biff, Peo Sjoblom, and Max!!!

Could you guys or anyone out there explain what do field=1 an
criterial=1 in this line mean:

Selection.AutoFilter Field:=1, Criteria1:="*" & UserVal & "*"

I tried to further improve my worksheet where user can choose whic
column to perform the search--field_1 or field_3.
If user chooses field_1 then the search function will only searc
related string in column field_1 and vice versa.

Can excel bolded the search string in the output??

And how to fit the worksheet into related columns and eliminate th
un-used area.

Thanks in advance.

YJ

 

[Max]

You're welcome ! I'm not proficient enough in vba to answer your follow
through queries. Hang around awhile for insights from others to flow-in
here. Or, you may want to try a new post in .programming.