CODEWITHSUNDEEP
exceldatabasefilterformulacriteria
Mirko FATE
Mirko FATE

Reputation: 3

FILTER formula with both row and column condition resulting in #VALUE error

I have been able to successfully use the FILTER formula to isolate values for columns and rows, and I have used it with multiple criteria as well.

For some reason it does not work when I try to include both a row and a column based filter.

I have managed to get around it by just having a column with the UNIQUE formula there per each entry. The issue with this is that is not a volatile formula and had to drag the formula across the thousands entries that I have...

I am not that good with coding or excel in general so I am a bit out of my depth here (I have attempted to and failed at researching a solution online that I could adapt).

These are the cells that I want to filter Row1 to 6
These are the cells that I want to filter Row1 to 6

I would like the column with the unique name to not be a dropped down formula, but to be - similarly to the Cohort one - dependent on the size of the database (to be encased in those blue lines)

Row filter (working):

=FILTER(OfficeForms.Table[Cohort],NOT(ISBLANK(OfficeForms.Table[Cohort])),"")

Column filter (working):

=FILTER(OfficeForms.Table[@[Your Name (SURNAME, Forename)]:[Your Name (SURNAME, Forename)3]],(NOT(ISBLANK(OfficeForms.Table[@[Your Name (SURNAME, Forename)]:[Your Name (SURNAME, Forename)3]]))),"")

My attempt at combining them:

=FILTER(OfficeForms.Table[@[Your Name (SURNAME, Forename)]:[Your Name (SURNAME, Forename)3]],(NOT(ISBLANK(OfficeForms.Table[Cohort])))*(NOT(ISBLANK(OfficeForms.Table[@[Your Name (SURNAME, Forename)]:[Your Name (SURNAME, Forename)3]]))),"")

Upvotes: 0

Views: 59

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

If I am not wrong and mistaken from the output posted in the OP, then you could also use the following formula to accomplish the desired output. This doesn't needs to use a LAMBDA() helper function.

enter image description here

• Formula used in cell A8

=HSTACK(TAKE(OfficeForms.Table,,1),TOCOL(DROP(OfficeForms.Table,,1),1))

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

It's hard to figure out exactly what you want from your textual description, but to reproduce your output screenshot from your input data, you can use something like:

=HSTACK(TAKE(Table9,,1),BYROW(DROP(Table9,,1),LAMBDA(arr, CONCAT(arr))))

enter image description here

enter image description here

Obviously, if your example is not representative of your actual data, this solution may give unexpected results.

Upvotes: 0

Related Questions