Filter Massive Data by Cell Value on Specific Column

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Feb 19, 2014

• #2

You could use a parameter query using SQL. That would allow you to only import the data that you want instead of importing your 43K rows of data then removing all but 80 rows.

 

Upvote 0

J

JPWRana

New Member

Joined

May 30, 2013

Messages

29

• Feb 19, 2014

• #3

Jerry, how do I use a parameter query using SQL? Is SQL an Excel thing?

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Feb 20, 2014

• #4

SQL is a database language. You can find many articles on the web that provide an overview.
It's not limited to being an Excel thing. It can be used through Excel, but also used by other applications that manage databases.

This video shows how to setup an SQL Parameter Query. The example uses an Excel data source and similar steps can be done to import data from Access using a Parameter to filter by your Log Numbers in Column B.
https://www.youtube.com/watch?v=P9cUYpXIKsU&list=UUlBYq5cbpioOFBqor6SIIBw&feature=c4-overview


Tushar Mehta has a nice set of articles that illustrate how to build queries through a series of examples.
Build Queries

 

Upvote 0

J

JPWRana

New Member

Joined

May 30, 2013

Messages

29

• Feb 20, 2014

• #5

Jerry, this was/is really useful. I went ahead and created a query from Excel and saved that specific query, that way I only have to call up the query, and instead of having ALL the data, I get only the columns I need, and do a partial filter for what I need.

Is there a way where I can filter for what the value is in a specific cell in excel? That is my new biggest issue right now.

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Feb 20, 2014

• #6

JPWRana said:

Is there a way where I can filter for what the value is in a specific cell in excel? That is my new biggest issue right now.

Click to expand...

Near the end of the Video, he describes how to set up the query so it gets the criteria parameter from an Excel cell. Is that what you are wanting?
If not, please clarify.

 

Upvote 0

J

JPWRana

New Member

Joined

May 30, 2013

Messages

29

• Feb 21, 2014

• #7

Are you sure the youtube video works? I couldn't get it to work.

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Feb 21, 2014

• #8

Yes, i can run the video from my computer and iPhone.

 

Upvote 0

J

JPWRana

New Member

Joined

May 30, 2013

Messages

29

• Apr 7, 2014

• #9

Jerry... it worked! In fact, it worked better than what I had hoped for!

Now I want to apply it like an array (for a different issue altogether).
I want to use the parametrized query kinda like vlookup, but can't seem to get it.

I would want A2 to get me what I want in B2/C2/D2/E2 just like the video says... except that when I want to do it in like an array sort of function, where A3 will get me what I want in B3/C3/D3/E3, etc... that won't work.

How can I array this?

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Apr 7, 2014

• #10

JPWRana said:

Now I want to apply it like an array (for a different issue altogether).
I want to use the parametrized query kinda like vlookup, but can't seem to get it.

I would want A2 to get me what I want in B2/C2/D2/E2 just like the video says... except that when I want to do it in like an array sort of function, where A3 will get me what I want in B3/C3/D3/E3, etc... that won't work.

How can I array this?

Click to expand...

Sorry, but I'm not understanding what you are trying to do. Could you post a screen shot of example inputs and the desired result?

 

Upvote 0