Can you run the equivalent of a select query in Excel? (1 Viewer)

Big Pat

Registered User.
Local time
Today, 13:09
Joined
Sep 29, 2004
Messages
555
If you have data in one Excel worksheet, can you output a subset of it to another sheet in the same workbook, based on criteria set on another sheet?

For example:
  • Criteria sheet: User selects various values
  • Data sheet: All the raw data, refreshed periodically, could be up to 50,000 rows x 40 columns
  • Output sheet: A subset of data e.g. 50 rows x 6 columns with additional calculated columns, properly formatted
I would normally link my sheet to Access, create a select query based on criteria input via a form and then re-export to a different Excel file, but I need to do the whole thing in Excel this time.

How should I go about this? What’s the general approach?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:09
Joined
May 7, 2009
Messages
19,169
You would normally record a macro fir that.
 

Albert Dicosta

Registered User.
Local time
Today, 06:09
Joined
Sep 7, 2018
Messages
16
I also tried to run the query in Excel but still didn't get solution. I'm also finding same solution.
 

Big Pat

Registered User.
Local time
Today, 13:09
Joined
Sep 29, 2004
Messages
555
If you want to use Google Sheets, I found that the QUERY function does exactly what I need and it's really simple. The first 40 seconds of this will explain how. Note that you don't have to select all columns. You could use "SELECT A,C,J" or whatever.

https://www.youtube.com/watch?v=CE77nmWzjaY
 

Big Pat

Registered User.
Local time
Today, 13:09
Joined
Sep 29, 2004
Messages
555
What about a Pivot Table?

I did attempt that, but I found it impossible to get the formatting exactly as I wanted and also to have extra calculated columns alongside the "filtered" data. Maybe someone with a lot more pivot table expertise could make it work, but Google's QUERY function does it for me.
 

Users who are viewing this thread

Top Bottom