Autofilter giving unexpected results!

Hi Experts

I have a ss of 30,000 product lines from 15 suppliers. It also shows sales year to date.

I want to filter and delete so I eventually end up with the top 100 sellers from each supplier.

I switched on Autofilter for all columns, selected one supplier and then selected top 100 items in sales - expecting to get the top 100 sales for that supplier.

Instead, I got all the lines for that supplier that appear in the top 100 overall sales!

I checked that I definitely filtered suppliers before I filtered on sales.

What did I get wrong?

Thanks

Colin

are u any good with advanced filter coz u cud do it easier with that

No. I'm not, but I'm willing to learn!

What do I need to do?

Colin

Originally Posted by Colin Vicary

No. I'm not, but I'm willing to learn!

What do I need to do?

Colin

My knowledge of Excel is something less than 10%, the problem here is that I do not know how far the 'something' goes.

I cannot see that the Autofilter would be dynamic this way, it would apply two simple filters giving the results you see.

The only way I know of to achieve your lists is to sort the lists over Supplier, then add a break beteen each supplier with the column headings copied down to the start of the new supplier, then select each supplier and Filter the data.

The break could (of course) be a new worksheet, (ie, sort, select the first line of the next supplier and cut/paste all following lines to a new sheet, repeat etc until the last supplier)


It would be nice for someone to provide a 'dynamic' filter, (and then this post can be ignored)

Good luck.

note, a small amount of VB code could produce those sheets at the press of a button, but you would need someone who knew how . . .
---