Return rows that contain value from multiple tables without blanks

[alecsi]

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

[#2 · Accepted solution]

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

[#3]

@alecsi 

Alternatively that could be Power Query solution which returns structured table

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

[#4]

@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

[#5]

@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. 

[#6]

@alecsi 

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

[#7]

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

[#8]

@alecsi 

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

[#9]

@alecsi 

You are welcome, glad to help

[#10]

@Lorenzo that was very kind of you! I appreciate it. Alternatives are always welcomed 🙂 very helpful, thanks!

Best wishes!
alecsi

[#11]

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