LET formula problem

[alecsi]

Hi guys,

can you have a look on this? My LET formula does not do the job, something is missing and I can't figure out what. I attach excel file and explain below what's needed:

- Sheet1, Sheet2, and Sheet3 contain one table table each.

- each table has 12 columns

- in the first 9 columns (A-I), I insert manual values, and in the last 3 columns (J-L) I have formulas

- in Sheet10 I have a big table that gathers all the information from Sheet1, Sheet2, and Sheet3 without blank rows. Here is where I have the LET formula

Problem is that, because of the formulas that are located in the last 3 columns (J-L) in Sheet1, Sheet2, and Sheet3, the LET formula in Sheet10 does not ignore the blank rows. It displays them.

Can you edit my formula and add the function of ignoring blank rows that contain formulas?

Note that the way that this LET formula was built, was that it can ignore blank rows that do not contain formulas, and displays date formats as: 01/01/2023

=IFERROR(LET(
tables, VSTACK(
Table1,Table2,Table3),
isEmpty, LAMBDA(v, NOT(AND(ISBLANK(v)))),
data, FILTER(tables, BYROW(tables, isEmpty ) ),
clean, SUBSTITUTE( data, "", "" ),
VSTACK( Table1[#Headers], clean )
)+0,LET(
tables, VSTACK(
Table1,Table2,Table3),
isEmpty, LAMBDA(v, NOT(AND(ISBLANK(v)))),
data, FILTER(tables, BYROW(tables, isEmpty ) ),
clean, SUBSTITUTE( data, "", "" ),
VSTACK( Table1[#Headers], clean )
))

Many thanks in advance!

[#2]

@alecsi Best practice would demand not to have empty rows in structured tables. That defeats their purpose and strength. Simply resize the tables and use the VSTACK function. No need for anything more complicated.

Alternatively, in case you can not eliminate the empty rows beforehand:

=VSTACK(
Table1[#Headers],
TAKE(Table1,COUNTA(Table1[Column1])),
TAKE(Table2,COUNTA(Table2[Column1])),
TAKE(Table3,COUNTA(Table3[Column1]))
)

[#3]

@alecsi 

I agree with @Riny_van_Eekelen that the best practice is to not include the blank rows in the table because it does not play to the strength of the table - dynamic expansion.

If you must make do with the situation, you could use this to combine without blanks:

=LET(
    header, Table1[#Headers],
    stack, VSTACK(Table1, Table2, Table3),
    cleaned, FILTER(stack, TAKE(stack, , 1) <> ""),
    VSTACK(header, cleaned)
)

[#4 · Accepted solution]

@alecsi 

In addition to the very valid observations by my friends @Patrick2788 and @Riny_van_Eekelen , I find myself wondering whether it's even necessary to have three separate sheets with exactly the same sets of columns.

If they are referring--a no doubt they are--to different entities, different products, people, services, whatever, you can still differentiate if you

• create a single table with those 12 columns you already have
• add a column that contains the Identifier of whatever it is that makes Sheet1 differ from Sheet 2, etc

So I've done that with the Sheet labeled "Combined" -- I haven't taken the time to go to your sheet 10 to work on your LET question. Just wanted to demonstrate a combined database.

With that combined database, for what it's worth, Excel could still separately report on each entity whenever desired--and do the composites and whatever else. And you can add new rows pertaining to any one of the entities in any order. Just identify them.

[#5]

@Riny_van_Eekelen
@Patrick2788
@mathetes

Thank you all for your amazing replies! You're completely right, I will take some time to analize what you wrote and then implement the solution that fits the best. I appreciate that you came up with different solutions. This is one thing that makes this community a better place :)

Best wishes!
alecsi

[#6]

@alecsi 

I agree with the others that you shouldn't need to filter blank records from Tables because they have no reason to be there in the first place.

= LET(
    tables,   VSTACK(Table1,Table2,Table3),
    isEmptyλ, LAMBDA(v, OR(v<>"")),
    data,     FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
    VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
  )

Where it is an issue is when one uses a 3D range (in order to accept addition sheets without changing the formulas), since this, by its nature, is likely to be oversized

= LET(
    tables,   VSTACK(Table3D),
    isEmptyλ, LAMBDA(v, OR(v<>"")),
    data,     FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
    VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
  )


Table3D
=Sheet1:Sheet3!$A$2:$L$20

These accept any records with data in one of the first 9 fields.

[#7]

.. or with the pretty colours

= LET(
    tables,   VSTACK(Table3D),
    isEmptyλ, LAMBDA(v, OR(v<>"")),
    data,     FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
    VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
  )


Table3D
=Sheet1:Sheet3!$A$2:$L$20

I use direct cell referencing so rarely (here it is used to define a Name) that I thought I might as well re-enter the formula.  Shame 'Sheet1' is not yellow but the salmon pink shows well if you have a taste for that kind of thing.

[#8]

Recommended by payment

[#9]

@alecsi 

I appreciate that you came up with different solutions. This is one thing that makes this community a better place :)

In addition to making this community a great place, this variety of possible solutions is one of the things about Excel that makes it such a powerful tool: there are always several different ways to accomplish a result. It's exciting to play with those different constructions, different functions, different mental frameworks.