Filter Based on a list

1

Hello, I have read this post and I still can’t get my IN filter to work properly. I appreciate any additional help

table1-long list of inventory
ID,Location

Table2-list of tasks
LIST of IDS, Task

I would like to filter Table1 by the LIST OF IDs on each record of table 2, and return the list of locations.

Example Data:
Table1
1,Room11
2,Room12
3,Room13

Table 2
1,3 Replace Light Bulb

Formula I need help with: Return a list containing “Room11,Room13” for the task “Replace Light Bulb”

Thank you!

2

Hi @Nick_Solyom,

as usual, having a shared working example would help.
In the meantime, maybe this can be useful:

Cheers!

3

Here’s my example, thank you for reading and helping!

Here’s some extra information too. This is a to do list database for the entertainment industry, keeping track of a bunch of Lights.

Here are the problems to solve:

  • Handle a lookup for a range of channels IE 1-5

  • Check to see if they are on the same position

  • [ ] If Yes, append the additional unit #’s

  • [ ] If No, append the additional position names and unit numbers

  • Handle more than once match for a channel number, IE Ch 1181 which has 3 units

  • Check to see if filtered hookup table has more than 1 row

  • [] Check to see if all are on the same position

  •   [] If Yes, append the additional unit #’s

  •     [ ] Check for Type and Purpose being unique

  •   [ ] If No, append the additional position names and unit numbers
4

Dear @Nick_Solyom,

Just a quick attempt to support you going in the right direction.

Each “light unit” should be put in one table, with an unique ID that preferable gives an indication of the location, type and other criteria that are important for easy identification.

In your table “lookup” at the column “Ch Number” each instance should be unique too. At least my assumption is that this number is an identification of error.
You could add one column in this table “channel range” where for example “Ch Number” 1183, 1184 are grouped under “wrong color”, where 1183 is for example “red” and 1184 is “green”. (You know better)

An then with a 3rd. table you will use the above information to report, where again the display column is unique, with information on date checked / checked by / location.

And with lookup to the “light unit” table the unit can be selected and with a lookup to the channel list the “error type” can be reported and in the next column you can get it “automatically” displaying the channel range, based on the chosen “channel list ID”.

So overall it’s mostly to get the structure in a meaningful way for your use case, hopeful I have given you some right directions, otherwise feel free to add more info