Forum Discussion

NotSoFastEddie's avatar
NotSoFastEddie
Brass Contributor
Sep 26, 2024

Do not generate a row in sheet if a specific cell is blank in the base sheet

The LET statement in this worksheet is courtesy of one of the community members.  I have tried pulling it apart and have had limited success.

 

Here is the ask.  There is one sheet that contains the base data.  The data is a list of products and their specifications.  In some cases, the specifications (cell) is empty and should not be included in the resulting spreadsheet.

The current resulting worksheet looks like this.  The rows (circled) should not appear as the value for the spec is blank "String,"

 

It should look like this.

 

I have attached the sample spreadsheet.  Hoping someone can help me out of this one.

 

  • m_tarler's avatar
    m_tarler
    Steel Contributor

    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)) )

     

    • NotSoFastEddie's avatar
      NotSoFastEddie
      Brass Contributor

      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

Resources