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


--
YJL
------------------------------------------------------------------------
YJL's Profile: http://www.excelforum.com/member.php...o&userid=28693
View this thread: http://www.excelforum.com/showthread...hreadid=484200

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

Provide some examples. Be very specific!

Biff

"YJL" wrote in message
...

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

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

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

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

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" wrote in message
...

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

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

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

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

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)),(R OW(A$1:A$4)-1)+ROW(A$1)),ROWS(A$1:A1))),"")

Copy down until you get blanks.

Will return:

abcde
edabc

Biff

"YJL" wrote in message
...

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

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

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)),(R OW(A$1:A$4)-ROW(A$1))+1),ROWS(A$1:A1))),"")

Biff

"Biff" wrote in message
...
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)),(R OW(A$1:A$4)-1)+ROW(A$1)),ROWS(A$1:A1))),"")

Copy down until you get blanks.

Will return:

abcde
edabc

Biff

"YJL" wrote in message
...

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

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

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

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

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" wrote in message
...

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

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

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

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

Hi!

Here's your file:

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

Biff

"YJL" wrote in message
...

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

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

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" wrote in message
...
Hi!

Here's your file:

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

Biff

"YJL" wrote in message
...

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

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

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

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

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" wrote in message
...

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

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

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" wrote in message
...

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

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

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
------------------------------------------------------------------------
YJL's Profile: http://www.excelforum.com/member.php...o&userid=28693
View this thread: http://www.excelforum.com/showthread...hreadid=484200

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

"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_usin g_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
--

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

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


--
YJL
------------------------------------------------------------------------
YJL's Profile: http://www.excelforum.com/member.php...o&userid=28693
View this thread: http://www.excelforum.com/showthread...hreadid=484200

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

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
--