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
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]]))),"")
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))))
Obviously, if your example is not representative of your actual data, this solution may give unexpected results.