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

Dear Gurus . ...

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

Please help. Thanks in advance.

YJL

Provide some examples. Be very specific!

Biff

"YJL" <[email protected]> wrote in message
news:[email protected]...
>
> Dear Gurus . ...
>
> I have a worksheet with 5 columns and 400 rows. All contents are
> sentences.
> I use Vlookup but it only can show result that matches the input
> exactly.
> How to ask vlookup to retrieve all the sentences that match my single
> input?
> And how to ask vlookup shows multiple searching result? (It stops after
> getting the first from top)
>
> Please help. Thanks in advance.
>
> YJL
>
>
> --
> YJL
> ------------------------------------------------------------------------
> YJL's Profile:
> http://www.excelforum.com/member.php...o&userid=28693
> View this thread: http://www.excelforum.com/showthread...hreadid=484200
>

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

.
.
.

Results

1 abcde fghij klmno pqrst uvwxy

2 fghij klmno pqrst uvwxy abcde

4 deabc fghij klmno pqrst uvwxy


-->show all related rows with "abc"

Hope my text explanation is clear.

Please advise.

YJL

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

"YJL" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "YJL" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> YJL
> ------------------------------------------------------------------------
> YJL's Profile:
> http://www.excelforum.com/member.php...o&userid=28693
> View this thread: http://www.excelforum.com/showthread...hreadid=484200
>

Thanks Biff, I got part of it done. However, I couldnt get multiple 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. I have attached my file for your reference. COuld you kindly look it. It is almost done. Thanks in advance.

YJL

Hi!

Here's your file:

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

Biff

"YJL" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks Biff, I got part of it done. However, I couldnt get multiple
> 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. I
> have attached my file for your reference. COuld you kindly look it. It
> is almost done. Thanks in advance.
>
> YJL
>
>
> +-------------------------------------------------------------------+
> |Filename: help.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4035 |
> +-------------------------------------------------------------------+
>
> --
> YJL
> ------------------------------------------------------------------------
> YJL's Profile:
> http://www.excelforum.com/member.php...o&userid=28693
> View this thread: http://www.excelforum.com/showthread...hreadid=484200
>

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> Here's your file:
>
> http://s45.yousendit.com/d.aspx?id=0...T2HYG420LQN5T2
>
> Biff
>
> "YJL" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Thanks Biff, I got part of it done. However, I couldnt get multiple
>> 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. I
>> have attached my file for your reference. COuld you kindly look it. It
>> is almost done. Thanks in advance.
>>
>> YJL
>>
>>
>> +-------------------------------------------------------------------+
>> |Filename: help.zip |
>> |Download: http://www.excelforum.com/attachment.php?postid=4035 |
>> +-------------------------------------------------------------------+
>>
>> --
>> YJL
>> ------------------------------------------------------------------------
>> YJL's Profile:
>> http://www.excelforum.com/member.php...o&userid=28693
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=484200
>>
>
>

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

>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

"YJL" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> YJL
> ------------------------------------------------------------------------
> YJL's Profile:
> http://www.excelforum.com/member.php...o&userid=28693
> View this thread: http://www.excelforum.com/showthread...hreadid=484200
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> YJL
> ------------------------------------------------------------------------
> YJL's Profile:
> http://www.excelforum.com/member.php...o&userid=28693
> View this thread: http://www.excelforum.com/showthread...hreadid=484200
>

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

"YJL" wrote:
> .. 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
'-----------

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks Biff, Peo Sjoblom, and Max!!!

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

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

I tried to further improve my worksheet where user can choose which column to perform the search--field_1 or field_3.
If user chooses field_1 then the search function will only search 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 the un-used area.

Thanks in advance.

YJL

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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--