Searching multiple columns and showing results

%3CLINGO-SUB%20id%3D%22lingo-sub-2119782%22%20slang%3D%22en-US%22%3ESearching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119782%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3EI%20have%20approx%201200%20service%20providers%20with%20a%20range%20of%20information%20for%20each%20on%20a%20spreadsheet.%26nbsp%3B%20Currently%20when%20we%20need%20to%20find%20a%20particular%20provider%2C%20we%20have%20to%20search%20through%20by%20sorting%20or%20filtering%20and%20its%20really%20messy.%26nbsp%3B%20I%20would%20like%20to%20set%20up%20a%20'front%20page'%20where%20people%20can%20search%20by%20State%2FArea%20and%20Service%20Provider%20and%20the%20result%20will%20appear%20below%20with%20the%20whole%20row%20of%20information%20for%20each%20of%20the%20providers%20that%20meets%20the%20search%20criteria.%26nbsp%3B%20So...I%20have%202%20questions.%26nbsp%3B%201.%20is%20this%20even%20possible%3F%26nbsp%3B%202.%20how%20can%20i%20do%20it%3F%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2119782%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121639%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961520%22%20target%3D%22_blank%22%3E%40valissa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22this%22%3F%20Was%20something%20supposed%20to%20be%20attached%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20has%20given%20what%20appears%20to%20be%20a%20good%20start.%20I'll%20just%20add%20that%20FILTER%20can%20work%20with%20multiple%20criteria%2C%20as%20well%20as%20AND%20or%20OR%20connections%20between%20those%20criteria.%20My%20intention%20was%20not%20to%20do%20it%20all%20for%20you%2C%2C%20but%20to%20point%20toward%20a%20solution.%20Here's%20another%20resource%20if%20you%20need%20to%20pursue%20it%20further%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20also%20might%20find%20useful%20(although%20quite%20a%20bit%20more%20unwieldly)%20the%20far%20older%20functions%20that%20allow%20all%20kinds%20of%20retrieval%20from%20full%20datasets%20such%20as%20you%20have.%20The%20various%20functions%20that%20begin%20with%20%22D%22%20such%20as%20DGET%2C%20which%20have%20been%20around%20for%2025%20years%20or%20more%2C%20are%20quite%20functional%20if%20you%20don't%20have%20access%20to%20FILTER.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121339%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961520%22%20target%3D%22_blank%22%3E%40valissa%3C%2FA%3E%26nbsp%3BPerhaps%20I'm%20not%20understanding%20your%20needs%2C%20but%20in%20the%20attached%20file%20I%20have%20filtered%20all%20providers%20in%20the%20NSW-NTD%20SYD%20area%2C%20where%20%26lt%3B%3CNDIS%20registered%3D%22%22%3E%26gt%3B%20is%20%22Accommodation%22%20OR%20where%20the%20%26lt%3B%3CAFTER%20hours%3D%22%22%3E%26gt%3B%20is%20%22City%20of%20Ryde%22.%3C%2FAFTER%3E%3C%2FNDIS%3E%3C%2FP%3E%3CP%3ECorrect%3F%20If%20so%2C%20it%20wasn't%20too%20difficult%20or%20messy%20and%20it%20too%20about%20ten%20seconds%20to%20achieve%20this.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I'm%20not%20correct%2C%20you%20can%20perhaps%20clarify%20the%20selection%20that%20you%20expect%20to%20see.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121266%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121266%22%20slang%3D%22en-US%22%3EThanks%2C%20but%20I%20need%20to%20be%20able%20to%20define%20my%20search%20results%20by%20more%20than%201%20criteria.%20I%20started%20with%20something%20similar%20to%20this.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121254%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121254%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961520%22%20target%3D%22_blank%22%3E%40valissa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20simple%20example%20of%20what%20can%20be%20done%20with%20the%20FILTER%20function.%20I've%20created%20a%20list%20of%20the%20providers%20that%20allows%20you%20to%20simply%20select%20them%20(alphabetically%20listed%20in%20a%20drop-down%20list).%20Once%20you've%20done%20that%20all%20of%20the%20relevant%20rows%20from%20your%20database%20will%20appear.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121185%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121185%22%20slang%3D%22en-US%22%3E%3CP%3Eoops%2C%20Excel%202016%3C%2FP%3E%3CP%3EValissa%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121016%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20the%20sheet%20with%20the%20data.%26nbsp%3B%20It's%20all%20publicly%20available%20information.%26nbsp%3B%20thank%20you%20so%20much%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20provider%20has%20a%20single%20row%20for%20information%20and%20I%20am%20using%20Office365%3C%2FP%3E%3CP%3Evalissa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119807%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961520%22%20target%3D%22_blank%22%3E%40valissa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPossible%3F%20Yes%3C%2FP%3E%3CP%3EHow%3F%20That%20depends%20on%20the%20structure%20of%20your%20data.%20When%20I%20read%20your%20description%2C%20I%20%22see%22%20a%20large%20spreadsheet%20with%20blocks%20of%20information%20on%20each%20provider.%20Blocks%20with%20headers%20and%20perhaps%20sub-headers.%20Each%20provider%20could%2C%20for%20instance%2C%20occupy%2010%20columns%20and%204%20rows.%20If%20that%20is%20the%20case%2C%20you%20%3CSTRONG%3Ecould%3C%2FSTRONG%3E%20use%20Power%20Query%20(not%20on%20a%20Mac%20though)%20to%20clean-up%20these%20blocks%20of%20data%20and%20transform%20them%20in%20to%20one%20row%20for%20every%20single%20provider.%20But%2C%20this%20could%20be%20easier%20said%20than%20done.%3C%2FP%3E%3CP%3EIf%20Ii%20misunderstood%2C%20and%20you%20already%20have%20one%20row%20for%20each%20provider%2C%20you%20are%20probably%20only%20a%20few%20steps%20away%20from%20a%20solution.%3C%2FP%3E%3CP%3EIt%20would%20be%20helpful%20if%20you%20could%20upload%20an%20example%20of%20your%20workbook%20with%20the%20provider%20data%2C%20so%20that%20the%20%22How%3F%22%20question%20can%20be%20answered.%20Kindly%20replace%20any%20private%2F%20confidential%20information%20like%20names%2C%20telephone%20numbers%20etc.%20by%20fake%20ones.%3C%2FP%3E%3CP%3EOh%2C%20and%20lastly%2C%20what%20Excel%20version%20are%20you%20on%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2123153%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20columns%20and%20showing%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2123153%22%20slang%3D%22en-US%22%3E%3CP%3Ethank%20you%20both%2C%20I%20managed%20to%20figure%20out%20how%20to%20do%20what%20i%20wanted%20to%20do.%26nbsp%3B%20Now%20I%20have%20a%20nice%20front%20sheet%20with%20search%20fields.%26nbsp%3B%20thanks%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi there,

I have approx 1200 service providers with a range of information for each on a spreadsheet.  Currently when we need to find a particular provider, we have to search through by sorting or filtering and its really messy.  I would like to set up a 'front page' where people can search by State/Area and Service Provider and the result will appear below with the whole row of information for each of the providers that meets the search criteria.  So...I have 2 questions.  1. is this even possible?  2. how can i do it?

thanks

8 Replies

@valissa 

Possible? Yes

How? That depends on the structure of your data. When I read your description, I "see" a large spreadsheet with blocks of information on each provider. Blocks with headers and perhaps sub-headers. Each provider could, for instance, occupy 10 columns and 4 rows. If that is the case, you could use Power Query (not on a Mac though) to clean-up these blocks of data and transform them in to one row for every single provider. But, this could be easier said than done.

If Ii misunderstood, and you already have one row for each provider, you are probably only a few steps away from a solution.

It would be helpful if you could upload an example of your workbook with the provider data, so that the "How?" question can be answered. Kindly replace any private/ confidential information like names, telephone numbers etc. by fake ones.

Oh, and lastly, what Excel version are you on?

@Riny_van_Eekelen 

here is the sheet with the data.  It's all publicly available information.  thank you so much

 

Each provider has a single row for information and I am using Office365

valissa

oops, Excel 2016

Valissa 

@valissa 

 

Here's a simple example of what can be done with the FILTER function. I've created a list of the providers that allows you to simply select them (alphabetically listed in a drop-down list). Once you've done that all of the relevant rows from your database will appear.

Thanks, but I need to be able to define my search results by more than 1 criteria. I started with something similar to this.

@valissa Perhaps I'm not understanding your needs, but in the attached file I have filtered all providers in the NSW-NTD SYD area, where <<NDIS Registered?>> is "Accommodation" OR where the <<After hours?>> is "City of Ryde".

Correct? If so, it wasn't too difficult or messy and it too about ten seconds to achieve this. 

If I'm not correct, you can perhaps clarify the selection that you expect to see. 

@valissa 

 

"this"? Was something supposed to be attached?

 

Anyway, @Riny_van_Eekelen has given what appears to be a good start. I'll just add that FILTER can work with multiple criteria, as well as AND or OR connections between those criteria. My intention was not to do it all for you,, but to point toward a solution. Here's another resource if you need to pursue it further:

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

You also might find useful (although quite a bit more unwieldly) the far older functions that allow all kinds of retrieval from full datasets such as you have. The various functions that begin with "D" such as DGET, which have been around for 25 years or more, are quite functional if you don't have access to FILTER.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...

thank you both, I managed to figure out how to do what i wanted to do.  Now I have a nice front sheet with search fields.  thanks for your help