Forum Discussion
Return rows that contain value from multiple tables without blanks
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 ) )
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 )
)
SergeiBaklan
thank you for answering. I appreciate your time.
I forgot to mention one thing and now I'm struggling to implement your 365 formula.I simplified the excel file and attached it below. TABLE1 gets its data from another table (REFERENCE TABLE), therefore the =FILTER formula sees the empty text as filled cell and does not ignore the blank rows. Is it possible to fix this?
It is a must that the =FILTER formula take the data from TABLE1, not from the REFERENCE TABLE
Regards,
Alecsi
I'm not sure what is your raw data source. If that's the range and you don't like to convert it into structured table , you may use it in formula like
=SUBSTITUTE( FILTER($K$12:$M$200, BYROW($K$12:$M$200, LAMBDA(v, NOT(AND(ISBLANK(v)))) ) ), "", "" )With that no need in intermediate table.
Initial formula if use ranges will be like
=LET( tables, VSTACK(Sheet1!$C$12:$E$25,Sheet2!$C$12:$E$27,Sheet3!$C$12:$E$28), isEmpty, LAMBDA(v, NOT(AND(ISBLANK(v)))), data, FILTER(tables, BYROW(tables, isEmpty ) ), clean, SUBSTITUTE( data, "", "" ), VSTACK( {"One","Two","Three"}, clean ) )- SergeiBaklan , thank you very much!! this solved my problem. I managed to integrate it in my spreadsheet and make it work. God bless you!
