OFFSET function with multiple table

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
  1. 365
I have 100++ sheet (table) with same format with difference high (total row not fix, will change). How to combine all table (sheet) in one sheet only. I prefer OFFSET function, any other solution is welcome. TQSM
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,775
no example - no details, sorry
but in short: add all tables to the Power Query Editor then use Append feature and result load into the eg. Summarize sheet

btw. update your Account Details about excel version, please
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,775

ADVERTISEMENT

here is an example for few tables
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,775
here is the way for
100++ sheet (table) with same format
example:
Power Query:
let
    Source = Excel.CurrentWorkbook(),
    Expand = Table.ExpandTableColumn(Source, "Content", {"Week 1", "Week 2", "Week 3", "Year", "Month", "Day"}, {"Week 1", "Week 2", "Week 3", "Year", "Month", "Day"}),
    Filter = Table.SelectRows(Expand, each not Text.Contains([Name], "Query"))
in
    Filter
tables.png

and so on...
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,775
read post#6 ;)

I assumed
- all tables are Excel Tables, not ranges that pretend to be tables
- there is nothing more than these tables

btw. you didn't update your profile about Excel version, can you do that?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,183
Messages
5,527,289
Members
409,757
Latest member
uzamr

This Week's Hot Topics

Top