## Forum Discussion

# Return rows that contain value from multiple tables without blanks

- Nov 24, 2023
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 )
)
```

- alecsiNov 24, 2023Copper Contributor
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

- SergeiBaklanNov 24, 2023MVP
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.

- SergeiBaklanNov 24, 2023MVP
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 ) )`

- alecsiNov 24, 2023Copper ContributorSergeiBaklan , thank you very much!! this solved my problem. I managed to integrate it in my spreadsheet and make it work. God bless you!