Advanced Filter/ Removing Duplicates

Occasional Visitor

How can I make an advanced filter permanent between two sheets? I basically have one sheet with a bunch of transactions (EX: buy one apple, buy two oranges, buy two apples etc.) Then I have another sheet that basically sums up my transactions (EX: Apples 3, Oranges 2). I want the two sheets to use the same names but on the second sheet get rid of the repeated names so apples only show once for example. I was able to use an advanced filter and copy over all the transactions from the first sheet and then paste it on second with unique responses but I didn't realize it was a one-time event and I would need to keep doing that as I add a transaction with different 'fruits'. How can I let these two sheets "talk" to each other?

1 Reply



Is it possible for you to post a file that either is your actual file or is a facsimile with any confidential or private info removed. I'm assuming that your apples and oranges example is itself not the real case. But if you could post a file of your own making that accurately reflects what you're dealing with, it would help target a response.


That said, if you have the most recent release of Excel, it contains some newly released Dynamic Array functions--SORT, UNIQUE and FILTER in particular--that I'm quite sure would work for you. How, exactly, would depend on the actual layout of your data. It will keep up with new additions to the basic table so long as it's arrayed as a real Excel Table.


If you are the type (as I am) that likes to figure these things out for yourself if at all possible, give those functions a try. They make it possible to build-in to a workbook the kinds of operations you've just gone through "manually." If more help is needed, come back with a representative file for us to work with.