Forum Discussion
How to filter by multiple and values
Hello there,
I've been racking my brain for the last day or so to try and come up with a solution to no avail.
Basically an overview of what I am trying to achieve:
Object 1 has qualities 1, 2, 4
Object 2 has qualities 1, 4, 5
Object 3 has qualities 4, 6, 7
I am trying to make a function where I list the qualities of the object regardless of the positioning of the qualities (1, 2, 4 or 1, 4, 2, or 2, 1, 4 etc)I want and it will return a list of objects which fit my description.
So if I searched for qualities "1, 4" It would return "Object 1, Object 2".
But if i searched for "4, 1, 2" only Object 1 would return as it is the only argument which has all those qualities.
There may be times where an Object has more values associated to it than I am searching for. I still want the function to take that into consideration. For example:
Object 4 has qualities 1, 2, 3, 5, 6
Object 5 has qualities 4, 6, 3, 8, 2
I am searching for qualities "2, 3, 4, 6" It should return Object 5.
I hope this makes sense, if anyone can provide any insight on how I can solve this problem it will be very much appreciated.
Thanks, Ashley
1 Reply
- Arul TresoldiIron Contributor
It's ridicoulous how that's easy with the googlesheet program instead of the client excel on a desktop... (using the =SPLIT function you can split the cell "1, 2, 4" in some columns, then you check how many corrispondences are in the each line and pick up the best.
With no =SPLIT function (as far as I know), you have to work with the "Split datas in columns" feature, but that's manual and not automatic (you have to do it whenever you need a new row with new Objects). Once you have the numbers of the qualities splitted in columns on the row of the Object, it's a match point!