Forum Discussion
Do not generate a row in sheet if a specific cell is blank in the base sheet
NotSoFastEddie on one hand that formula is very clever on another hand it feels like there could be an easier way but without re-doing the whole thing this should work:
=LET(
features_tbl, FILTER(baseData!A1:AE10,(baseData!A1:A10<>"")),
data, DROP(DROP(features_tbl, 1, 2),, -1),
keys, DROP(HSTACK(TAKE(features_tbl,, -1), TAKE(features_tbl,, 2)), 1),
specNames, DROP(DROP(TAKE(features_tbl, 1),, 2),, -1),
attrcode, XLOOKUP(specNames, Table_Specifications[Attribute name], Table_Specifications[[Classification Attribute ]], "not found"),
typevalue, XLOOKUP(specNames,Table_Specifications[Attribute name], Table_Specifications[Feature Type], "not found"),
a, SEQUENCE(ROWS(data)),
b, SEQUENCE(, COLUMNS(data)),
o, CHOOSECOLS(
EXPAND(
HSTACK(
CHOOSEROWS(keys, TOCOL(IF(b,a))),
TOCOL(IF(a,attrcode)),
TOCOL(IF(a,typevalue))&","&TOCOL(data)),, 7, ""
),
2, 3, 4, 5, 6, 7, 1
),
FILTER(o,TOCOL(data)<>"")
)
so basically on line 10 before the current CHOOSECOLS is added "o," and then on line 18 after the closing parantheses of that CHOOSECOLS is added a comma and then on line 19 is added a FILTER to get rid of the lines you don't want. note: I notice there are still some lines with "undefined" included and not sure if those should be or should not be included. If not you can tweak the FILTER to be:
FILTER(o, (TOCOL(data)<>"")*(TOCOL(data)<>"undefined"))
or better yet add another line to define that TOCOL(data) like so:
f, TOCOL(data),
FILTER(o, (f<>"")*(f<>"undefined"))
or maybe reject any line with NON-uppercase characters:
f, TOCOL(data),
FILTER(o, (f<>"")*EXACT(f,UPPER(f)) )
m_tarler - THANKS VERY MUCH! I was certainly stuck. Had the right idea but the I was trying to filter too early.
Great job thanks again