SQL query or equivalent filter based on condition

[Fractalis]

Hello to all,

There is a way to use SQL sentences to do queries in a table in Sheet1 (A to F) to put the output in
Sheet2 the values in columns A, B and E from table in Sheet1 when values in column B are equal to 5?

I mean, is possible to do this within the same workbook? or a equivalent VBA code?

Thanks in advance.

 

[Marcelo Branco]

Maybe this

In Sheet1 H2 put this formula (assuming headers in row 1, data beginning in row 2)
=B2=5

Let H1 empty

Go to Sheet 2
In A1, B1, C1 type in, respectively, the headers of columns A, B and E

Data > Advanced Filter

Pick Copy to another location

In List Range use the small arrow (RefEdit) at the right to select the data range (including headers) in Sheet1

In Criteria Range use the small arrow to select H1:H2 in Sheet1

In Copy to enter A1:C1

Ok

Done!

M.

 

[Fractalis]

Oi Marcelo,

Thank you for your help.

I've tried your solution. Only works for me if I select the destination cell in the same sheet1, If I select other sheet as destination, I receive
the error saying that the data only can be copied to active sheet.

Thanks again for the help.

 

[Fractalis]

Besides that, one more question:

With your solution with advanced filter, is possible to concatenate source range to use in List Range?

I ask, because I have similar databases in other sheets for which I need to filter by values in column B.

If not possible to concatenate ranges, I'll have to do this procedure 3 or 4 times.

Thanks for help so far.

 

[Marcelo Branco]

ADVERTISEMENT

Oi Marcelo,

Thank you for your help.

I've tried your solution. Only works for me if I select the destination cell in the same sheet1, If I select other sheet as destination, I receive
the error saying that the data only can be copied to active sheet.

Thanks again for the help.

It worked perfectly for me.
Put the formula in H2 in Sheet1

Then go to Sheet 2 and being there use
Data > Advanced Filter

M.

 

[Marcelo Branco]

See the topic

Extract Data to Another Worksheet

in

Excel Advanced Filter Introduction

M.

 

[Marcelo Branco]

ADVERTISEMENT

Besides that, one more question:

With your solution with advanced filter, is possible to concatenate source range to use in List Range?

I ask, because I have similar databases in other sheets for which I need to filter by values in column B.

If not possible to concatenate ranges, I'll have to do this procedure 3 or 4 times.

Thanks for help so far.

Maybe you can use the macro recorder and adapt the code to automate the process.

M.

 

[Fractalis]

Hello Marcelo,

Thank for your help, answers and the useful link you shared. Has very good examples. It seems fails for me because I was launching filter from source sheet and not being in destination sheet as in the example.

One doubt, if I use the advanced filter in a Macro, do you know if it is possible to
Use an array variable in the "criteria range"? Or only is possible to use criteria ranges written in a range in a sheet?

Thanks for the help!

Regards

 

[Marcelo Branco]

One doubt, if I use the advanced filter in a Macro, do you know if it is possible to
Use an array variable in the "criteria range"? Or only is possible to use criteria ranges written in a range in a sheet?

Thanks for the help!

Regards

I *think* adv filter works only with ranges in a sheet.

You are welcome and thanks for the feedback.

M.