Feb 09 2021 09:11 PM
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
Feb 09 2021 10:36 PM
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?
Feb 10 2021 02:01 PM
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
Feb 10 2021 07:50 PM - edited Feb 10 2021 07:51 PM
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.
Feb 10 2021 08:20 PM
Feb 10 2021 11:17 PM
@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.
Feb 11 2021 05:45 AM
"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.
Feb 11 2021 01:55 PM
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