Autofilter Top 10

[richardjshaffer]

Hi,

I have a query I just can't figure out how to solve.

We have a list of stores, some are regular stores and some are outlets (1 column shows if it's regular or outlet).

We need to show the top 10 using the autofilter top 10 option - it works fine for all stores - but we want to show the top 10 regular stores.

Using the top 10 filter and the stores column filter too to exclude the regular stores, we don't get the top 10 regular stores which we want, it only shows eg 8 regular stores because 2 outlet stores are hidden as they are in the top 10 filter.

Hope this is clear, any ideas? I don't really want to copy all the regular stores somewhere else to do the top 10 on as it's a complex spreadsheet already.

many thanks
Richard

 

[Fazza]

Hi, Richard.

It sounds like you have an identifier either 'regular store' or 'outlet'. Just combine two top 10 datasets. So, using a query table instead of the autofilter the SQL would be something like below. Modify for your headers.

HTH, Fazza

SELECT TOP 10 store, type, value
FROM source
WHERE type='regular store'
ORDER BY value DESC
UNION ALL
SELECT TOP 10 store, type, value
FROM source
WHERE type='outlet'
ORDER BY value DESC

 

[richardjshaffer]

thanks for your help, though we don't use databases yet.

i was hoping there might be an excel solution, but I'm not sure there is.

 

[Fazza]

Hi, Richard. It is an Excel solution. I didn't spell it out in this post but have in many others. Or googling for examples and help on 'query tables' should find more info. Cheers, Fazza