Need Some HELP with Formula

[msm66]

Hi 

I will appreciate if somebody can help me to find the right formula or function. not sure if it is possible only through Formula or needs VBA. I tried UNIQUE and SORT but it did not Work. 

I attached both the Screenshot and Excel file.

I guess the best way to ask my question is with an example. Let's say I choose "Blue" "red" "yellow" "white" from column H (I already created a checkbox for this column and link each cell)

I would like to come up with a solution that each time I select different Colors from Column "I", it searches all the cells in column "inventory 1" and those cells that contain the selected colors (In this case any cells that contain "blue" "red" "yellow" "white") be extracted to column "New 1". 

And I can do the same for "Inventory 2", "Inventory 3"..."Inventory 5" and results for each will be shown in Column "NEW 2" "NEW3" ..."NEW 5"

[#2 · Accepted solution]

@msm66 

Assuming that the color will always be the first word of the item description, you can try this in L2:

=IFERROR(INDEX(A$2:A$8,SMALL(IF(MMULT(--(LEFT(A$2:A$8,FIND(" ",A$2:A$8&" ")-1)=TRANSPOSE($H$2:$H$8)),--($J$2:$J$8)),ROW(INDIRECT("1:"&ROWS(A$2:A$8))),""),ROWS(L$2:L2))),"")

I don't have the filter function to test it, but this might work also (Ctrl+Shift+Enter after keying or copying it to the formula bar):

=FILTER(A$2:A$8,MMULT(--(LEFT(A$2:A$8,FIND(" ",A$2:A$8&" ")-1)=TRANSPOSE($H$2:$H$8)),--($J$2:$J$8)))

Also, not there is a trailing space for "Pink " in Column H that was removed. I attached the file as well.

[#3]

@JMB17 , @msm66 

Yes, it works. Another variant could be

=IFERROR(FILTER(A1:INDEX(A:A,COUNTA(A:A)),
    MMULT(--ISNUMBER(
          SEARCH(TRANSPOSE(FILTER($H$2:$H$8,$J$2:$J$8)),
                 A1:INDEX(A:A,COUNTA(A:A)))),
          --FILTER($J$2:$J$8,$J$2:$J$8))
),"- no inventory -")

[#4]

@JMB17 You're Rock man. The first Formula is the one I need. First is much more understandable and simple and sharp as a tack. The problem with second formula is that it finds the ones start with the color. Let's say somebody else who does not have any idea how the formula works updates the inventory every day and by accident put "HAT YELLOW" Instead of "yellow hat", However, the solution will be creating a new column next to products and call it Color. That way you make it more specific. For sure, The first formula shows everything that contains the color. In my case, works perfectly. Thanks, A lot for taking the time to solve the problem.

[#5]

Thank you Sergio Balkan as well taking your time reading my problem. @JMB17 solution was a Bomb. yes, it works.

[#6]

@msm66 

IMHO, both formulas check the start of the texts, that was the only reason why I adjusted second one.

[#7]

Yes, I thought about using SEARCH in case the color was not the first word. But, then I thought you could run into a potential issue with the color being embedded as part of another word in the item description (like how "red" appears in the word "scared").

But, moving the color to it's own cell would certainly solve all of those potential issues. And, you could dispense with the LEFT(x, FIND()) part. And, from Sergei's post, it looks like the filter function can work, so it could be further simplified (I just don't have the filter function to play with it).

[#8]

@Sergei Baklan 

I was thinking what if we have multiple columns with checkboxes to filter? can we use "AND/OR" in "FILTER"?

An example is been attached:

in this example, let's say how we can filter inventory 1 to search for items selected in both columns H and J? 

A formula that shows everything in Inventory 1 that includes (blue, black, red) and/or (Shirt, jacket or sweater)???

[#9]

@msm66 

For OR condition you may + another MMULT, for AND multiply them as here (for OR)

formula is

=IFERROR(FILTER(A1:INDEX(A:A,COUNTA(A:A)),
--(MMULT(--ISNUMBER(
          SEARCH(TRANSPOSE(FILTER($H$2:$H$8,$J$2:$J$8)),
                 A1:INDEX(A:A,COUNTA(A:A)))),
          --FILTER($J$2:$J$8,$J$2:$J$8))+
MMULT(--ISNUMBER(
          SEARCH(TRANSPOSE(FILTER($L$2:$L$8,$N$2:$N$8)),
                 A1:INDEX(A:A,COUNTA(A:A)))),
          --FILTER($N$2:$N$8,$N$2:$N$8))
>0)),"- no inventory -")

[#10]

@Sergei Baklan
Sorry for the late response. It works perfectly. you make it look super simple. Thanks, man.

[#11]

verdade mesmo

[#12]

liere para os inscrito o office pois precisamos para comentar melhorias e desenvolvedores

[#13]

* liberem

[#14]

@Sergie Baklan
I am facing a problem with the formula. It looks like we have to select at least one item from column L otherwise it won't show the results for column H. "no inventory will be the result.
in other words, let's say I do not want to select any "type" just want to check what color is available in inventory 1. or vise versa I just want to check the "type" or sometimes both.
For instance, in this example, if you uncheck all column "M" the result will be "No inventory" however in inventory 1, we have items that their color is either blue or red which we have already selected. So it can not be true.
Do you have any idea how we can adjust the formula? by this assumption that also it works if there will be more than two columns of checkboxes, not only two columns.
Your thoughts regarding this will be greatly appreciated.

[#15]

@msm66 

Yes, that's a bug. I added couple of more error checks

=IFERROR(
   FILTER(A1:INDEX(A:A,COUNTA(A:A)),
      (
         IFERROR(
         --MMULT(--ISNUMBER(
            SEARCH(TRANSPOSE(FILTER($H$2:$H$8,$J$2:$J$8)),
                 A1:INDEX(A:A,COUNTA(A:A)))),
            --FILTER($J$2:$J$8,$J$2:$J$8)),
         SEQUENCE(ROWS(A1:INDEX(A:A,COUNTA(A:A))),1,0,0))   +
         IFERROR(
         --MMULT(--ISNUMBER(
            SEARCH(TRANSPOSE(FILTER($L$2:$L$8,$N$2:$N$8)),
                 A1:INDEX(A:A,COUNTA(A:A)))),
            --FILTER($N$2:$N$8,$N$2:$N$8)),
         SEQUENCE(ROWS(A1:INDEX(A:A,COUNTA(A:A))),1,0,0))
      >0)
   ),
"- no inventory -")

Result is in attached file

[#16]

@Sergei Baklan No word! I am speechless. You are a magician I guess.
I have a minor issue, but not sure if it is possible to make it even more intelligent. I attached the file.
I changed the color "Blue" to "Light Red".
Let's say you just want to see the results for "Light Red" not "Red".
Is there any trick you think might work? I tried to put it between " " so maybe it can tell the difference and only show me the "LIGHT Red" but it did not work. 

Basically the formula search for everything that contains the marked checkbox. but how it can separate the "Red" from "light Red" and return the one has been checked since both contain "Red".

[#17]

@msm66 

I see. Let try this modification

=IFERROR(
   FILTER(A1:INDEX(A:A,COUNTA(A:A)),
      (
IFERROR(
      MMULT(
         TRANSPOSE(--ISNUMBER(
               SEARCH($H$2:$H$8,
                   TRANSPOSE(A1:INDEX(A:A,COUNTA(A:A)))))),
          --$J$2:$J$8-
            TRANSPOSE(
                MMULT(
                   --TRANSPOSE($J$2:$J$8),
                   (--ISNUMBER(SEARCH($H$2:$H$8,TRANSPOSE($H$2:$H$8))) -
                    MUNIT(ROWS($H$2:$H$8)))
               )
           )
    ),SEQUENCE(ROWS(A1:INDEX(A:A,COUNTA(A:A))),1,0,0))   +
         IFERROR(
         --MMULT(--ISNUMBER(
            SEARCH(TRANSPOSE(FILTER($L$2:$L$8,$N$2:$N$8)),
                 A1:INDEX(A:A,COUNTA(A:A)))),
            --FILTER($N$2:$N$8,$N$2:$N$8)),
         SEQUENCE(ROWS(A1:INDEX(A:A,COUNTA(A:A))),1,0,0))
      >0)
   ),
"- no inventory -")

[#18]

@Sergei Baklan 

It is Working!!!

I do not know what to say. but literally did not understand the code you wrote at first glance until I dig deep and learned some of the functions. All I can say you're absolutely a genius. 

Thank you. 

Expect me for more questions in the future lol

[#19]

@msm66 

You are welcome. Perhaps it could be found more simple solution if write from scratch without patching initial formula one by one, but that's only possible improvement.