Search multiple words within row

[bsh3p]

Hi all, first post - thanks in advance for taking the time to take a look.

I've looked and looked for an answer to my issue but no luck, hoping somebody can please help.

My file looks like below but with 65k rows.

[TABLE="width: 300"]
<tbody>[TR]
[TD]Hotel Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]Best Western[/TD]
[TD]Washington[/TD]
[/TR]
[TR]
[TD]The hilton[/TD]
[TD]new york[/TD]
[/TR]
[TR]
[TD]Intercontinental the Barclay[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]Hollywood Hilton[/TD]
[TD]hollywood[/TD]
[/TR]
</tbody>[/TABLE]

I want to search for a hotel using multiple search terms and return row number or potentially highlight any row that contains all words exactly (don't want case sensitivity). Also, as I will be repeating this process I want to define a named range and enter the search criteria into the sheet rather than changing the formula each time as below.

[TABLE="width: 200"]
<tbody>[TR]
[TD]Search Criteria[/TD]
[/TR]
[TR]
[TD]hilton[/TD]
[/TR]
[TR]
[TD]new york[/TD]
[/TR]
</tbody>[/TABLE]

As above, search terms are "hilton" and "new york" so I want to return row 3 from the data file. I do not care about rows 4 or 5 that contain only one of these values (Hilton/New York).

As in example above with "The hilton", I cannot simply use Ctrl-F as hotel names are not exact - "Hilton New York" does not appear in a single cell. As per other examples given, hotel name may or may not contain location and doesn't have a set standard! Of course

Using Office2010 if that helps, hopefully above makes sense.

Regards, B

 

[Aladin Akyurek]

Something like...

=LOOKUP(9.99999999999999E+307,MATCH("*"&E2&"*"&"|"&E3,A:A&"|"&B:B,0))

 

[footoo]

Alternatively, particularly if there might be more than one match, you could do a filter :

Sub v()
[A:B].AutoFilter
[A:B].AutoFilter field:=1, Criteria1:="=*" & [E2] & "*"
[A:B].AutoFilter field:=2, Criteria1:="=*" & [E3] & "*"
End Sub

Code could be added to return whatever details required (row number(s), etc.) for the filtered result.

 

[bsh3p]

That worked!!! Thanks Aladin

I won't use the word issue, however from doing testing it seems to return the row value that comes first. Is there a way to return all row values where the combination exists? As there are many instances where there are many Hiltons in the one city for example. Perhaps given this scenario, it may be easier to do a conditional formatting formula entry to highlight all matching rows?

For my learning benefit, may I please ask what all the 9's are for in the formula?

Thanks so much!

Thanks also to footo, haven't touched coding before so Aladin's solution seemed the easiest option to implement for my skill level!

B

 

[footoo]

Thanks also to footo, haven't touched coding before so Aladin's solution seemed the easiest option to implement for my skill level!

All you have to do is copy the macro I posted, press Alt+F11, and paste the macro in the window on the right.
To run the macro go to Developer/Macros.
It can be assigned to a button if you wish.

The macro does all you have requested in your last post - it filters the data by the required criteria.

 

[Aladin Akyurek]

Are you saying that we can have many Hiltons in new York?

 

[bsh3p]

Thanks footoo for the guidance, I got that to work. Easy when you know how hey!

Unfortunately, I just realised that my data is spread across 2 tabs. I assume this is a data limit as tab 1 has 65536 rows. Not sure if there's a way around this but if not, is there a way to get this macro to work still? Sooooorrry!

Ideally I guess, search criteria goes into tab 1 to search data across tabs 2 and 3. Search results then display in tab 1.

@Aladin, yes there are often multiple hotels with similar names in the same location. Most likely, when I get the search function right I can add more search criteria with more specific words or even an address to deal with this. My data is more complicated, just trying to make this simple for people to assist to get me on the right track.

eg search criteria may be "hilton" "midtown" "mid-town" "new york" "avenue" "nyc" "ave"
to give me the row corresponding to "New York Hilton Midtown" address "1335 6th Avenue, New York, NY 10019, USA"

PS I don't work for Hilton, just a nice easy to spell example :D Easier to type than some others!

Thanks again

 

[Aladin Akyurek]

Try to present a representative sample along with representative search criteria and associated output.

 

[footoo]

Unfortunately, I just realised that my data is spread across 2 tabs. I assume this is a data limit as tab 1 has 65536 rows. Not sure if there's a way around this but if not, is there a way to get this macro to work still? Sooooorrry!

Ideally I guess, search criteria goes into tab 1 to search data across tabs 2 and 3. Search results then display in tab 1.

So your data exceeds 65536 rows? (Maybe time to update to a newer Xl version!)

Before I spend time writing a macro, please confirm that is the way you want to go.
Also would need info like the name of the criteria/results sheet, the names of your data sheets, what columns used, how many header rows.

Alternatively, and hopefully, you could just run the macro separately for each sheet.
You would need to have the criteria on each sheet - but could input to one sheet and have linked formulas on the other sheet.

 

[bsh3p]

Thank you

I am consulting my IT department about this before I waste anybody's time!

:D