Best way to handle multiple data sources in 2010

[JCTalk]

Hiya,

I currently have 5 multiple data sources that I lookup certain columns and combine in a single separate spreadsheet, then copy and paste as values so its not constantly updating (there's about 40,000 rows of data). The data sources are only updated once a week.

I'm wondering if there is a better way to handle the data. I know of data modelling using Power Query / Power Pivot to combine the files linking by unique ID field, but my concern is that the end users of the combined file are on 2010 (so no built in tools like in 2013/2016) and my business is unlikely to want people installing "addons" (hence the current formula/lookup method).

DB's are also out of the question. I have to do all this in Excel alone.

If I used Power Query (on just my machine) to create the combined data source, could I then have another spreadsheet for all those people without the power addons that just uses MS Query to connect to the table created in the combined data source file?

Cheers for your advice guys.

 

[Chihiro]

For most simple imports (without too much data transformation or calculations) MS Query will work just as well as Power Query.

Only caveat being that you'd need to use VBA to make connection string dynamic etc and that you also need to be somewhat familiar with SQL query statements. Be mindful of specific syntax needed in MS Query (such as file name, sheet name, Column being nested in "[]" etc).