Merging formulas into bigger filter with multiple conditions

**Pantoffol** · 03-23-2023, 04:36 AM

Hi,

I suspect a simple question, but answer I can't find yet.

I want to merge these formulas:

=ALS(E3=43;FILTER(Actions!B2:D29;Actions!B2:B29='Operator 1'!D3;" "))
=ALS(E4=43;FILTER(Actions!B2:D29;Actions!B2:B29='Operator 1'!D4;" "))

Such that the values are displayed below each other.

What to do:
If E3=43, then it should filter Actions!B2:D29 for the values where B2:B29 equals 'Operator 1'!D3. The same for E4 with Actions!B2:B29='Operator 1'!D4;. And actually this should continue up to E29.

If I merge them together as: =ALS(E3=43;FILTER(Actions!B2:D29;Actions!B2:B29='Operator 1'!D3;" ");IF(E4=43;FILTER(Actions!B2:D29;Actions!B2:B29='Operator 1'!D4;" ")))

Then it only returns the filter result of the first function. Tried all sorts of things, but without help it doesn't seem to work.

Pop_voor_TL_maken.xlsm

**TMS** · 03-23-2023, 05:29 AM

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

**Pantoffol** · 03-23-2023, 06:01 AM

Done!

. Thanks.

**Glenn Kennedy** · 03-23-2023, 07:20 AM

I do not see E3 or E4 with a value of 43.... where is the formula? Where are the values of 43?????

**Pantoffol** · 03-23-2023, 08:07 AM

Well, the values 43 have been made white text to hide. Forget to mention that part. They are calculated with the function InteriorColor. But you may also note a hard 43 to test a formula. I took out my own formula as it did not work out. Does not matter where it goes, for now only need to figure out a way to do it.

**Glenn Kennedy** · 03-23-2023, 08:23 AM

So we were looking for an invisible number!!!! No wonder I couldn't find it.

I'll make ONE guess. If this is incorrect, please add your expected answers, manually calculated IN the file.

=FILTER(Acties!B2:D29,(Acties!B2:B29='Operator 1'!D3)+(Acties!B2:B29='Operator 1'!D4))

see file.

**Pantoffol** · 03-23-2023, 08:56 AM

Sorry for that Glenn. Just looking for help from the experts here.

Let me pick row 2 as an example.

If E3 contains '43', then filter Acties!B2:D29 with condition Acties!B2:B29='Operator 1'!D3.

That should also be for: If E4 contains '43', then filter Acties!B2:D29 with condition Acties!B2:B29='Operator 1'!D4.

So in the file attached, if E2 contains '43' and E3 does not, it should return:
Attachment 822870

If E3 contains '43' and E2 does not, it should return:
Attachment 822872

If they both contain '43' it should return:
Attachment 822873

File:Pop_voor_TL_maken (1) (1).xlsm

**Glenn Kennedy** · 03-23-2023, 09:17 AM

OK. Try this:

=LET(A,Acties!B2:D29,B,IF(E2=43,FILTER(A,INDEX(A,,1)='Operator 1'!D3)),C,IF(E3=43,FILTER(A,INDEX(A,,1)='Operator 1'!D4)),D,VSTACK(B,C),FILTER(D,INDEX(D,,1)<>FALSE))

**Pantoffol** · 03-23-2023, 09:21 AM

You are truly amazing! And if I want to extend this with more rows in the same way, how do I adjust the formula? What do I need to add?

**Glenn Kennedy** · 03-23-2023, 09:27 AM

Original (modified slightly - red)

Formula:

Please Login or Register  to view this content.

To add a 3rd row (purple):

Formula:

Please Login or Register  to view this content.

Clear??

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

**Pantoffol** · 03-23-2023, 09:33 AM

Very clear!

Will add reputation and mark it as solved. Thanks also for the feedback on the way of posting the question. Will keep those in mind next time.

**Pantoffol** · 03-23-2023, 11:57 AM

Hi Glenn Kennedy,

I do have an extra question. You may also now the answer to this, as you helped me out real good already. Let me try to explain.

In my file, which I attach, you can double click to make cells in column D green on the page 'Operator 1'. Upon doing that, it will give the color code in cell E which equals to '43' (I made sure it is unhidden haha). On takenlijst it now shows the filter of 'Acties' column B,D and D based on the value in column D on page 'Operator 1'. So that works now.

But, I want to add something. On the page 'Operator 1' column B and C are also filled. I want it to place that before the filtered result on the page 'Takenlijst'.

So for example: if D3 on 'Operator 1' is made green, on takenlijst it should filter B:D of "Acties" for values of which B equals to D on 'operator' ánd add 'Branchekennis' and 'Automotive' before each of these rows.

Don't know if I am asking to much.

Pop_voor_TL_maken GK2 2.xlsm

**Glenn Kennedy** · 03-23-2023, 01:06 PM

OK. I may have completely misunderstood what you're doing.

But...

I do not see why you are highlighting 1 op 1 training in green 3 times. It is merely repeating the same results. Look at YOUR file. There are 18 rows, 3 blocks of 6 repeated rows.

Is that REALLY what you wanted???

Now look at this file. It doesn't have the Branchekennis & Automotive yet...BUt, I think it may be closer to what you actually want.

**Pantoffol** · 03-23-2023, 03:36 PM

Well, you are partially right. It is the combination with the values in column B&C on 'Operator 1' that makes it unique.

Here's the thing.

A reviewer indicates on 'Operator 1' the action to be performed for each part. So, let's take the first one as an example:

That is industry knowledge & automotive. An evaluator can select up to three actions there: 1 on 1 training, create assignment and create video training. If a value is selected, the actions that belong to it and are on the 'actions' sheet must be placed on the task list sheet. But for that the values in columns B & C of the sheet 'Operator 1' must be placed. Because that makes them unique.

Hope this explains a bit.

**Glenn Kennedy** · 03-24-2023, 04:50 AM

Uff. Indigestion overnight led to me thinking about this one for far too long.... Eventually inspiration struck!!

Formula:

Please Login or Register  to view this content.

See file.

**TMS** · 03-24-2023, 05:14 AM

Indigestion overnight …

TMI, Glenn

**Pantoffol** · 03-24-2023, 05:28 AM

Guess I have to thank you and indigestion, because you are a true hero! Can't thank you enough. Now I will spend some hours trying to understand what you did.

**Glenn Kennedy** · 03-24-2023, 05:30 AM

Originally Posted by TMS

TMI, Glenn

Black pudding, scallops and bacon...

**TMS** · 03-24-2023, 06:10 AM

No, really TMI

**Pantoffol** · 03-24-2023, 07:32 AM

If I however try to replicate it, it says invalid formula and it points to the MAP part of the formula. While it is working in the part you put in there. Any idea why?

**Glenn Kennedy** · 03-29-2023, 06:48 AM

Please update your profile (model it on mine) using the information that you see on your account page.

**Glenn Kennedy** · 03-29-2023, 06:59 AM

I've checked online. You are using a Mac and have almost the most up-to-date version of O365 for the Mac.

As far as I can see, there >>should<< be no problem. So... I need to see a file where it does NOT work for you.

**Pantoffol** · 03-29-2023, 07:49 AM

Updated Glenn, sorry for that.

Actually it is the exact file you send me, but I will resend it. If I copy the formula and place it below in empty space, it says invalid formula.

Pop_voor_TL_maken GK3.xlsm

Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Re: Merging formulas into bigger filter with multiple conditions

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Filter Formula with multiple conditions

[SOLVED] Both formulas return same number, but one is bigger than the other. How to fix?

[SOLVED] Merging multiple workbook values instead of formulas

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions