lookup single value in one sheet, return multiple results from the other sheet

Originally Posted by cvanoosbree

i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)

what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255

the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)

is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset

after some MASSIVE googling, i have stumbled accross this

B1 = Search box (txt field)


A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number


A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number


and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on


2 things i cannot recitify..


1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results..


is there any resolution to the above

cheers & thanks

Unless I'm missing something, why can't you just perform a Data Filter on the database, either filtering the list in place, (you've already got thr drop down arrows set up) or if necessary using the Advanced Data Filter option to populate your results table?

Your requirement seems to be to filter a Unique name from Column A, e.g. find the single item 'Camera' rather than the two rows, both containing a reference to 'camera'. In either case it's not immediately obvious why the Filter won't achieve what you want.

Neither can I see how the results you are showing on the results sheet are related to the keyword 'boats', and the OFSSET() functions you're using seem an unnecessarily complicated way of identifying variable ranges of items.

Rgds

Hi
here is a macro solution to your problem (see file attached) Type your search word in B1 of Results sheet and press the button.
Ravi

Originally Posted by Richard Buttrey

Unless I'm missing something, why can't you just perform a Data Filter on the database, either filtering the list in place, (you've already got thr drop down arrows set up) or if necessary using the Advanced Data Filter option to populate your results table?

Your requirement seems to be to filter a Unique name from Column A, e.g. find the single item 'Camera' rather than the two rows, both containing a reference to 'camera'. In either case it's not immediately obvious why the Filter won't achieve what you want.

Neither can I see how the results you are showing on the results sheet are related to the keyword 'boats', and the OFSSET() functions you're using seem an unnecessarily complicated way of identifying variable ranges of items.

Rgds

hi richard,

i agree with you that the autofilter should do the trick, and it does, however, feedback states that there is just to many to look through a drop down box. hence the search box option i am trying to put together.

Originally Posted by ravishankar

Hi
here is a macro solution to your problem (see file attached) Type your search word in B1 of Results sheet and press the button.
Ravi

hi ravi,

i have attempted to search for something within the modified spreadsheet you put together.

1 thing i noticed just by opening the data tab is that the data is now all over the place, but i have redone that tab (copy & paste from origonal)

and the 2nd, is that no result actually comes up. i keep getting that box saying that nothing is found

=====

is there a way to make that search box a LIKE statement? eg, i want to type in computer .. .and have it display the relevant hits .. eg,
Computer - Desktop
Computer - Laptop
etc

cheers for your assistance

HI
The macro searches for phrase entered in Cell B1 of Results tab in col B1 to B225 of Data tab. For example EQUIP returns 157 hits. try Drill. you can use partial terms ex Dri Computers or laptop or desktop is not in col B of data. so they can't be pulled out
Ravi

Originally Posted by ravishankar

HI
The macro searches for phrase entered in Cell B1 of Results tab in col B1 to B225 of Data tab. For example EQUIP returns 157 hits. try Drill. you can use partial terms ex Dri Computers or laptop or desktop is not in col B of data. so they can't be pulled out
Ravi

hey ravi, i finally figured it out.. its CASE SENSITVE.. however, looking up the wrong column for search.. needs to look through Data!A2:A225 .. i will see if i can modify the modules you have accordingly

Originally Posted by cvanoosbree

hey ravi, i finally figured it out.. its CASE SENSITVE.. however, looking up the wrong column for search.. needs to look through Data!A2:A225 .. i will see if i can modify the modules you have accordingly

well, i tried .. & i failed .. can you tell me what part of hte module needs to be modified so the search actually looks at Data!A2:A225

also, is there a way to remove case sensitive ? eg, change everything to lcase on data & keyword?

cheers & sooooo close

HI
I have modified the codes to allow for both upper and lower case input and to search in col A instead of B.
Ravi

Originally Posted by ravishankar

HI
I have modified the codes to allow for both upper and lower case input and to search in col A instead of B.
Ravi

ravi,

just wanted to say thanks .. YOU ROCK