CODEWITHSUNDEEP
excelfiltering
user2327631
user2327631

Reputation: 141

Recreating a Google Spreadsheets QUERY() in Excel

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!

Upvotes: 14

Views: 46794

Answers (4)

anakic
anakic

Reputation: 2978

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

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

Query function "hello world" example

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).

Upvotes: 5

minxpe
minxpe

Reputation: 1

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.

Upvotes: 0

disruptivxs
disruptivxs

Reputation: 1

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

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

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

Upvotes: 10

Related Questions