Is there a filter delimiter in Google Spreadsheets?

Question

I'm trying to build a list of servers in a Google Spreadsheet. Each server has multiple services, and I would like to be able to select a single service and see all the servers providing that service.

The problem I'm facing is the multiple services part. There's too many to provide a column for each service. Is there any sort of delimiter I can specify in the filter?

Or is there maybe a better way I can do this?

Answer 1

There's a possibility to do that, but its a bit complicated. I've prepared an example file for you to play around with: Is there a filter delimiter in Google Spreadsheets?

In the first sheet (DATA) all data is presented. One helper column is added to capture the logic. Here's the logic:

=IF(LEN(B1)>1;INDEX(SPLIT(B1;";"),0,VLOOKUP(FILTERED!$B$1;D:E;2;0));B1)

Based on the value entered in sheet FILTERED' theVLOOKUP` formula will find a number, that will be used for the index function. That will show only a specific column, given by the split function.

In the FILTERED sheet the result of the DATA sheet will be in filtered by the entered value in FILTERED:

=FILTER(DATA!A:B;DATA!C:C=B1)

Just have a look into the example, to see how it works...

Answer 2

There are no filter delimiters or other tools which will achieve what you are looking for... that I know of.

One approach to the problem would be to make it a list of services rather than a list of servers.

For example, your data might look like:

server1     dns;dhcp;dc
server2     dc
server3     dns;dhcp;dc

the new data would look like:

dns      server1
dhcp     server1 
dc       server1 
dc       server2
dns      server3 
dhcp     server3 
dc       server3

Of course, this may mess with your other plans for this list.