Jul 11 2020 08:21 PM
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"
Jul 11 2020 09:44 PM
Solution
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.
Jul 12 2020 01:34 PM
Jul 12 2020 02:11 PM
Jul 12 2020 02:13 PM
Jul 12 2020 02:31 PM
IMHO, both formulas check the start of the texts, that was the only reason why I adjusted second one.
Jul 12 2020 02:49 PM
Jul 15 2020 08:46 PM
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)???
Jul 16 2020 09:28 AM
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 -")
Jul 17 2020 07:05 PM
Jul 17 2020 08:02 PM
Jul 18 2020 09:14 AM - edited Jul 18 2020 09:18 AM
@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.
Jul 18 2020 01:26 PM
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
Jul 18 2020 03:11 PM
@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".
Jul 20 2020 04:26 AM
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 -")
Jul 21 2020 07:19 PM
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
Jul 22 2020 04:44 AM
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.
Jul 11 2020 09:44 PM
Solution
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.