asked on

Excel - sorting and summarizing from another sheet

Hi all,

Let's say I have a random list of items on a sheet:

What is the best and most efficient way to bring only the Fruit items into another sheet in the same workbook?

Roy Cox

I would suggest AdvancedFilter automated by VBA. Attach an example and I'll add some code.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

Here's a quick example. If it does what you want then let me know, I'm finishing now but I can provide a fuller explanation and any changes in the morning
Fruit.xlsm

Roy Cox

Just had a look at the other suggestion, but I think you will find advancedfilter much faster, especially on bigger lists

Go-Bruins

ASKER

Thanks for the replies. Any way to do this w/o code and macros?

Roy Cox

Formulas will be more complicated especially as you do not know how many formulas are required, but a PivotTable would work
Fruit.xlsm

Go-Bruins

ASKER

Thanks again. I was going to try some kind of =filter formula, but I think the pivot table is a more flexible.

Sorry - but one more question. On the pivot table, let's assume I set the filter to "Vegetable", how do I get the running count in the "Grand Total" field?

SOLUTION