Advanced Filter>>Unique and SumIf

[iknowu99]

I often find myself marking the unique records only and applying the SumIf formula in the next column. This time I was going to work on a new sheet and learned that the filter works only in same sheet.

My questions is a general one. I would like to know how the community does such a task.

 

[schielrn]

usually if it is a large list and I probably won't be doing it again, I would use the advance filter for uniques copy to another location on the same sheet and then copy and paste it to the other sheet.

There are also formulas that you can use and they can dynamically update on your other sheet. i don't know of one off the top of my head, but I have seen them.

Lastly you can always resort to VBA code and create a generic sub that you can use in many instances.

Hope that gives some ideas.

 

[cornflakegirl]

Advanced filter can be used to copy to another sheet - you just have to select that sheet before you start the advanced filter process. Then when it comes to select the range to filter, just flip back to the original sheet - you won't get any error messages.

 

[iknowu99]

oops posted in lounge!

 

[iknowu99]

good call on the different sheets advice

 

[Fazza]

For a quick result like described I use a pivot table - to a new worksheet and delete the worksheet when I'm done. If a copy of the results is needed, a copy values only from the pivot table.

Regards, Fazza