Excel VBA Introduction Part 58.24 - SQL for Excel Files - Consolidate Worksheets with Derived Tables

By Andrew Gould

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can join this channel to get access to perks:

This videos demonstrates how to use derived table subqueries to consolidate multiple worksheets into a single list using the Group By clause and aggregation functions. You'll learn how to nest a Select statement in the From clause of an outer Select statement and how to reference columns from the subquery in the main query. You'll also learn how to apply the Group By clause to the rows returned by the subquery and how to add aggregated values, including grand totals.

Chapters
00:00 Topic List
00:41 The Basic Setup
02:40 Using Union All to Select All Rows
05:06 Creating a Derived Table Subquery
07:46 Adding a Group By Clause
10:00 Calculating More Aggregates
12:02 Grouping by the Source Worksheet
13:46 Adding a Summary Row
15:30 Changing the Grouping
16:08 Sorting the Query Results
19:15 Using a Derived Table to Remove the Helper Column

[bondniko]

Never before has my work been so productive and fun. Thanks indeed!

[internationalpolitics]

Hi Andrew this is what I was waiting for. Thanks a lot

[frikduplessis]

Another blockbuster tutorial👌 without Twilight 😃 Andrew Thank you so much, Mr 🐶 was also very interested in this SELECT nested in a SELECT in a SELECT 🙌 brilliant,

[tejamarneni]

Thank you Andrew. My second round interview is on Wednesday. They told me that this will also be a technical round with SQL. Hopefully I will crack this round as well. Thank you for your videos and support.

[zm]

Thank you Andrew for this video. I struggled always with 25 line limitation of SQL statements for VBA. If you can explain in future videos the best ways to go around that issue that would be fantastic. Thank you so much

[krn]

Thanks Andrew. Lots of work to hide a column. Lol.

[VincentHardwick]

Hi Andrew, great content as ever, and that sort helper column trick never gets old 👍 one quick suggestion - the audio in your videos is always at a noticeably lower volume than others on YouTube, and certainly lower than any ads which appear occasionally. Just so that we don't have to reduce the volume when the ad countdown begins (to avoid having our ears blasted!) and then increase it again after the ads so that we can hear you, any chance you could boost the volume in your recordings a bit please?

[KhalilYasser]

Thank you very much, my tutor. Can you please post the final sqlQuery?

[Munichandra_Reddy]

Hi sir,
When will you start ? Create topics
Just I am follow up.

[Munichandra_Reddy]

Hi sir,
I want to learn store procedure advanced level (best examples), if you know any URL and tutorial point please let me 🙏