Combine two filter formulas

[ExcelNewbie92]

Hello,

I would like to combine two filter formulas (or something equivalent) as they are pulling results from two different sheets:

=FILTER(DeadSummary!B3:E50000,(DeadSummary!B3:B50000>"0"))

The one above with:

=FILTER(Summary!D8:H50000,(Summary!D8:D50000>"0")) - In this one, would it also be possible to modify it to skip a Column? As in filter Column D, E, F, G, H and J (skipping Column I completely)

Is something like this possible? Been trying for hours but got stuck at this point. Do let me know if it's required an example sheet, can try to do one tomorrow.

Thank you in advance

[HansDouwe]

First question:
Please try

Please Login or Register  to view this content.

Secomd question:

Please Login or Register  to view this content.

[ExcelNewbie92]

Hello HansDouwe and thank you for your reply,

Sorry for lack of example sheet, I'm doing one at the moment (it was late and my brain had given up). Will attached it as soon as I finish

But your first formula gives me a "too few arguments" error and the second formula "too many"

Help.xlsx

[AliGW]

Try:

=IFNA(VSTACK(FILTER(DeadSummary!B3:E50000,DeadSummary!B3:B50000>0),FILTER(Summary!D8:H50000,Summary!D8:D50000>0)),"")

and:

=IFNA(VSTACK(FILTER(DeadSummary!B3:E50000,DeadSummary!B3:B50000>0),CHOOSECOLS(FILTER(Summary!D8:J50000,Summary!D8:D50000>0),1,2,3,4,5,7)),"")

0 does NOT need to be in inverted commas - it's not text.

[ExcelNewbie92]

Hello AliGW, Your second one doesn't give an error but the last 2 Columns are not showing up. Also if possible could it just be one formula pulling the values and adding everything?

I've finished the example sheet with some notes on it. Thanks for the zero information!

Help.xlsx

[AliGW]

Cheeky - you seem to have added extra criteria in the workbook. Please don't do this - you seem to have a habit of drip-feeding your requirements, and this just adds to your helpers' workload. Please declare EVERYTHING in your opening post.

New requirements:

From the DeadSummary starting at B3 to E3 all the way until B="0"

From Summary starting at D8 to J8 BUT ignoring Column I please.

Even better if all could be sorted automatically by Column A smaller to largest values

Anything else you haven't told us???

[AliGW]

Maybe try this:

=SORT(IFNA(VSTACK(FILTER(DeadSummary!B3:E50000,DeadSummary!B3:B50000>0),CHOOSECOLS(FILTER(Summary!D8:J50000,Summary!D8:D50000>0),1,2,3,4,5,7)),""))

[ExcelNewbie92]

Thanks again AliGW, and I do apologise, sometimes as I'm doing think of things that could also be done/improved, will try to avoid that in the future

That last formula almost does the trick! However the sorting is the only thing not right, due to the number not being in order and having letters I can only take into consideration the last 4 digits. That's why on sheet3 I have =RIGHT(B2,4) to then sort them accordingly. Just try to do the same manually which I would be happy to but it says that I can't change part of an array

What if I did the =RIGHT(B2,4) on the Summary and DeadSummary, would it be any easier for your formula to sort the numerical from smaller to largest?

[AliGW]

Any better?

=LET(a,IFNA(VSTACK(FILTER(DeadSummary!B3:E50000,DeadSummary!B3:B50000>0),CHOOSECOLS(FILTER(Summary!D8:J50000,Summary!D8:D50000>0),1,2,3,4,5,7)),""),SORTBY(a,RIGHT(INDEX(a,,1),4)))

[ExcelNewbie92]

AliGW, I'm speechless you are amazing!!! That did the trick. Thread solved

[AliGW]

You are welcome.

You know how to sign off the thread.