Advanced Filter Help - I DARE YOU TO SOLVE THIS ONE!

[joegio25]

Hi guys and girls, sorry for the re-post but I just figured out how to share my file on Windows SkyDrive.

https://skydrive.live.com/redir.aspx?cid=4cdbd283ac4cd6f6&resid=4CDBD283AC4CD6F6!102

In the attachment you will find a list of products named "list." The first column is the product number i.e. 111.32 The following 9 columns are SKU numbers. To the right of the list is a section titled "criteria." This is the criteria of which I am using to run the advanced filter on the product list. Below the criteria section, is the section to where I would like the advanced filter to copy the results to. Sounds easy but as you can see in the criteria section, there are multiple product numbers that match (111.32), BUT have different sets of SKU #'s. When excel goes to filter the table, it is only finding the one match for each column, and copying to the section below. As you see in the results table.

It is grouping similar strings and grouping them as one. I DON'T want it to group.

As you can see in the "criteria" section, there are 4 sets of critera (each outlined in a different color) that form the "criteria" section. So, there should be 4 sets of SKU #'s in each of the columns in the "copy to" section. BUT THERE IS NOT! Again, it is grouping the like items.

Is this achievable? I would like to stay away from VBA, being the novice I am. But, if that is how I must do it, than I have no choice.

Thanks everyone!

 

[p45cal]

I forgot to say that I needed to insert/repeat the criteria headers to make 4 criteria ranges before running this macro.

 

[Aladin Akyurek]

You're saying that because the same line in the criteria section occurs x times, you expect to see the same line in the results x times - I think.
I don't think the advanced filter was designed to work that way (what would have to happen if you were to do this but filter the list in-place? It hides (filters out) those rows that don't apply, it would have to add rows).
I suspect the key word is Filter here, rather than Query.

...

Possibly another way, if sql can get you what you want, is to query the table. A bit more involved to set up, but should be a lot more flexible.

Looks like I got the same results as you, using calculated criteria in advanced filter...

 

[joegio25]

Thanks for the input! P45CAL - That coding is definitely above my head. The only problem is the criteria list is dynamic. It grows and shrinks based upon the "Order Entry."

Text removed by Moderator

Thanks again!

 

[p45cal]

Possibly another way, if sql can get you what you want, is to query the table. A bit more involved to set up, but should be a lot more flexible.

I'm not adept at sql but I had a go. I created a named range SourceData as the 'table' to be queried, added a querytable to the sheet using (in xl2003) Data|Import External Data|New Database Query, chose Excel files, navigated to the self-same file, chose the SourceData table, then using the Query Wizard added the same criterion 3 times plus another, (only using the first 2 columns as a trial) just to see if it would duplicate the result rows. It didn't. So I went back in to edit the query using MSQuery and here's the picture and the sql statement in notepad (you can zoom in; it's just clear enough to see what's going on):

<embed src="https://www.box.net/embed/8926zrei5lsbmyp.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="500" width="700">

At the bottom you can see there are only 2 results rows.
Now if anyone adept at sql can devise an sql statement to get multiple rows up, it would be a small step to incorporate it, and later to write a bit of vba to manipulate the statement for other, future queries.

Anyone?

I suppose you could have multiple queries, as I had multiple criteria sets for the advanced filter, and stack them together. Maybe the multiple criteria sets is they way forward, if a whizz sql statement can't be conjured up to do the job in one hit.


ps. here's that source sql as text:
SELECT SourceData.Product, SourceData.`digit #1`, SourceData.`digit #2`, SourceData.`SKU #1`, SourceData.`SKU #2`, SourceData.`SKU #3`, SourceData.`SKU #4`, SourceData.`SKU #5`, SourceData.`SKU #6`, SourceData.`SKU #7`, SourceData.Vendor, SourceData.Category, SourceData.`Category #`, SourceData.Item, SourceData.`Part #`, SourceData.Qty, SourceData.UOM, SourceData.`Issue to WC no#`
FROM SourceData SourceData
WHERE
(SourceData.`digit #1`='111.32.3') AND (SourceData.Product=111.11) OR
(SourceData.`digit #1`='111.32.3') AND (SourceData.Product=111.11) OR
(SourceData.`digit #1`='111.32.3') AND (SourceData.Product=111.11) OR
(SourceData.`digit #1`='111.32.3') AND (SourceData.Product=111.32) OR
(SourceData.`digit #1`='111.32.3') AND (SourceData.Product=111.32) OR
(SourceData.`digit #1`='111.32.3') AND (SourceData.Product=111.32) OR
(SourceData.`digit #1`='111.11.1') AND (SourceData.Product=111.11) OR
(SourceData.`digit #1`='111.11.1') AND (SourceData.Product=111.32)