How to combine lists from multiple sheets into 1?

[stitchcreek]

Hi there,

I have a spreadsheet where multiple sheets are maintained by different users, however the layout of each is identical.

I'd like to create a sheet which pulls all of the lists into one large list, without any blanks.

I'd like this large list to always reflect the latest updates to the individual lists, meaning the length of the lists and number of blank rows may be different each time an update is made.

Can anyone help?

Any advice much appreciated!

 

[sandy666]

Use PowerQuery
- append all lists into one
- remove blanks
- load to the sheet
- use refresh connection on open workbook

 

[stitchcreek]

Use PowerQuery
- append all lists into one
- remove blanks
- load to the sheet
- use refresh connection on open workbook

I’m not familiar with PowerQuery, is that functionality already built into Excel?
If not, I need something that is...

 

[My Aswer Is This]

We would need more specific details:
Like what is the name of the Master sheet.

Where do you have data on your other sheets.

And when you say mutable sheets do you mean all other sheets

So update Master sheet when any change is made to any other sheet in the Workbook.

I know nothing about

PowerQuery​

And even if I did I would need more help knowing how to get it to do what i want.

 

[sandy666]

PowerQuery add-in - free from MS site - excel 2010/2013
Buil-in - 2016 and higher

 

[stitchcreek]

PowerQuery add-in - free from MS site - excel 2010/2013
Buil-in - 2016 and higher

I recently moved on to 2016.
So how do I use it?

 

[sandy666]

Add each list to PowerQuery editor (Data - From Table)
All headers should have the same name (if not - change the name)
Append all lists
Remove blanks
Close&Load to (Table, select cell)

--
Append Queries

 

[sandy666]

Simple example for three lists (with different headers) is here: Append Lists

if you will change something in source lists use Ctrl+Alt+F5 to refresh QueryTable (Green) or change refreshing options in Connection

 

[stitchcreek]

There isn’t a solution that automatically refreshes?

 

[sandy666]

there is:
- refresh every n minutes
- refresh data when opening the file
- refresh all
or
you can use VBA to refresh every time when you change andy source data but to me it doesn't make any sense