Recreating a Google Spreadsheets QUERY() in Excel

Question

I have a massive Excel Workbook that I use for tracking product titles and descriptions, and I'm trying to get it to generate .csv files for importing those products into eBay and my own website. I'm 99% of the way there, but I can't seem to find the Excel equivalent of Google Spreadsheet's QUERY() function, and there are two spots I need to use it:

1st, I need to populate a column in SheetB with the Product IDs in SheetA that have not been listed on the site. In Google Spreadsheets, I would do this with =query('SheetA'A:B,"select A where isblank(B)") but I can't for the life of me figure out the equivalent in Excel.

2nd, I need to take all the non-blank rows from four different sheets and put them together into one sheet.

There has to be something obvious I'm missing, but I'm missing it. Help me, magical internet people, you're my only hope!

Answer 1

The bad news is that Excel does not have an equivalent of QUERY()

The not quite so bad news is that you can roll your own.

Some possible ways:

1. Move your data to a database. You can do the whole thing in the database, or still use Excel as a front end (eg by using Get External Data to query the db, or build it all in VBA)
2. Use AutoFilters to query your data (this is not SQL queries...). There a plenty of examples on SO of this technique
3. Use Get External Data (in spite of the name, this can be self referencing) to query your data (this is SQL queries) See this answer for a starter

Answer 2

Have a look at this short video of a 3rd party Query() function for Excel.

Here's a 5s "hello world" gif as well:

To install it, you'll need the QueryStorm runtime, which is a free 4MB installer. It's like a package manager for Excel. After you install it, go to the QueryStorm tab in the ribbon, click Extensions and install the "Windy.Query" package (as show in the video).

[Disclaimer] I'm the 3rd party (author of QueryStorm).

Answer 3

Use Power Query in Excel. It will do the same thing and much more, although using g different techniques. Go to YouTube and search ExcelIsFun and MSPTDA

Answer 4

I was in need of doing the same, using INSERT>TABLE in Excel does the trick, from the table you create a pivot table and when you add columns and/or rows it the pivot

Answer 5

I know this post is 8 years old, but if anyone else is looking for a solution, you could try the following formula in cell C2

=INDEX('SheetA'$A$2:$A$10, SMALL(IF($D$2="", ROW($A$2:$A$10)-ROW($A$2)+1), ROW(1:1)))

This will look for empty cells in SheetA A Column and display ONE value in C2 cell. If you want to extract values from B column, repeat the same formula but replace 'SheetA'$A$2:$A$10 with 'SheetA'$B$2:$B$10.

Cons: 1. you will have to copy this formula to the right and manually replace the first bit of the formula. 2. you must copy down the formula to as many rows as there are values in the SheetA to ensure you don't miss any values.

Finally: After entering the formula, press CTRL SHIFT-ENTER to convert it into an array formula.

Answer 6

With props to @anakac for a very cool third-party add-in, the best answer to this problem without question is Matt's: Power Query. There's a tiny bit of a learning curve but it's an incredibly versatile tool that can create truly reusable and automated filter results.