SOLVED

# Return rows that contain value from multiple tables without blanks

Copper Contributor

# 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 should this work:

- each Sheet 1, Sheet 2 and Sheet 3 has one table that contains entries with start date & end date.

- SHEET10 will gather all the entries from Sheet 1, Sheet 2 and Sheet 3 without blank rows in between them.

- Each sheet contains calendar dates for 1 month only, for example:

Sheet 1 = will mark the begining of the year (January) from 1 jan. to 25 jan.

Sheet 2 = will contain dates from the next month (February) 25 jan. to 20 feb.

Sheet 2 = will contain dates from the next month (March) 20 feb. to 15 mar.

*** if it's easier for you, you can use in your solution a "help table" that gathers all the tables from Sheet 1, Sheet 2 and Sheet 3 in one single table >>> see SHEET10 , cell J1

Alecsi

10 Replies
best response confirmed by alecsi (Copper Contributor)
Solution

# Re: 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, "", "" ),
)``````

# Re: Return rows that contain value from multiple tables without blanks

Alternatively that could be Power Query solution which returns structured table

Query itself (build with UI only) is in attached file.

# Re: Return rows that contain value from multiple tables without blanks

@Sergei Baklan
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

# Re: Return rows that contain value from multiple tables without blanks

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.

# Re: Return rows that contain value from multiple tables without blanks

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

# Re: Return rows that contain value from multiple tables without blanks

@Sergei Baklan , thank you very much!! this solved my problem. I managed to integrate it in my spreadsheet and make it work. God bless you!

# Re: Return rows that contain value from multiple tables without blanks

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

# Re: Return rows that contain value from multiple tables without blanks

You are welcome, glad to help

# Re: Return rows that contain value from multiple tables without blanks

@L z. that was very kind of you! I appreciate it. Alternatives are always welcomed :) very helpful, thanks!

Best wishes!
alecsi

# Re: Return rows that contain value from multiple tables without blanks

@alecsi You're welcome & Thanks for your words. Appreciated

1 best response

Accepted Solutions
best response confirmed by alecsi (Copper Contributor)
Solution

# Re: 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, "", "" ),