Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
Need Some HELP with Formula

- Home
- :
- Excel
- :
- General Discussion
- :
- Need Some HELP with Formula

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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"

Labels:

18 Replies

Best Response confirmed by
msm66* (Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-12-2020 01:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-12-2020 02:11 PM

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-12-2020 02:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-12-2020 02:31 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-12-2020 02:49 PM

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).

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).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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)???

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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 -")
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-17-2020 07:05 PM

@Sergei Baklan

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-17-2020 08:02 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2020 09:14 AM - edited 07-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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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".

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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 -")
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-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.