Forum Discussion
Need Some HELP with Formula
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"
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.
18 Replies
- * liberem
- liere para os inscrito o office pois precisamos para comentar melhorias e desenvolvedores
- verdade mesmo
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.
- 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.
- 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).
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)???
