Forum Discussion
Return rows that contain value from multiple tables without blanks
Hello, need some help here. Please have a look. I need a formula that returns rows that contain values from multiple tables without blank rows. I attach the excel file here and explain below how...
It depends on each Excel platform you are. In any case it is better to transform ranges in sheets into structured tables.
Assuming
- you are on 365
- all tables have the same headers
- in not blank rows not necessary all cells are filled
- you use result as it is, i.e. no sorting, filtering
formula could be
=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 ) )
Seems too late alternative (on Book333):
=LET(
Tables, VSTACK(Table1,Table2,Table3),
Data, FILTER(Tables,
MMULT(--(Tables <> ""), SEQUENCE(COLUMNS(Tables)))
),
NoZero, SUBSTITUTE(Data, "",""),
TryNum, IFERROR(VALUE(NoZero), NoZero),
VSTACK(Table1[#Headers], TryNum)
)
