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 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
Thanks in advance!
Alecsi
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.
Alternatively that could be Power Query solution which returns structured table
Query itself (build with UI only) is in attached file.
