Remove duplicates -- "filter" is hard to understand
I frequently have marketing lists of customers. Then a separate dataset that is always updating contains a subset of those customers who made a transaction. My routine task is to remove customers who appear on the transaction sheet from the the marketing sheet so we can keep marketing only to the accounts that have not made a transaction. Simple, right?
In excel, I merely copy the account #s of the customers on the transaction list; take it over to the marketing list and paste it at the bottom of the corresponding column of accounts#; conditional format that column of account numbers to highlight the duplicates; then sort by that formatting so the duplicates are all in one chunk at the top; then delete them; then delete the account numbers that I pasted from the transaction sheet and now I have a fresh marketing sheet that contains only accounts that have not made a transaction. Libre Office Calc can duplicate this step-for-step except sort by formatting. So if I have 200 transactions scattered across a marketing list of 10,000 lines, it's a laborious task to delete them one by one, but I can't seem to figure out how to get them to group up for easy deletion.
If your answer is "use Filter" -- I have searched for this answer before posting this question -- then please explain to me like I'm 5 how to use that because I have tried, and tried and tried -- and even followed a youtube tutorial -- and the "filter" concept and execution is still extremely puzzling to me.
Thanks!
edit: I agree "filter" is pretty stragithforward for other applications, but to use it to remove duplicates, it's a really odd duck of a tool.
edit2: Conditional Formatting also identifies duplicates that the Filter function does not. Indeed, when I choose to copy the results of the filter, some of the duplicates have been removed but others are still there and, get this, still Conditionally Formatted. Which means the Conditional Formatting function identified duplicates that Filter for some reason did not. Is that worthy of a bug report?
edit3: removed the "Answered" green check due to this: https://ask.libreoffice.org/en/questi...
) to the left of the answer that solves your question.
filter (for kids): [data - more filters - standard filter - select column - options - no duplicates - copy output to] just try and play on sandbox data, is nice, but!:
the solutions by @Frants and @LeroyG are much better, less work, easy ... try that!
My primary task usually involves removing the duplicate AND the initial instance as well -- anything with a dupe is itself a dupe, in my mind; there's not like "original" and "dupe" -- they are both dupes of one another and both need to be removed. Filter only removes one duplicate, and leaves behind one instance. I'm sure there are other people who want that functionality, but it does nothing for me. I'm not sure how to use filter to do what I need.
@default_abuser: you may want to nail something with a screwdriver, but that's nonsense, what you really want is to remove the records from your marketing list which have made transactions and thus also appear in the transactions list ...
not! first to inject duplicates to then remove them together with the original ...
thus mark em with the vlookup method proposed by Frants and @LeroyG, filter to see only rows which have the marker, autofilter is sufficient for that, delete them ... straightforward and! 'dynamic', whenever you add customers to the transaction list you see them markered in the marketing list ...
The dogpile on VLOOKUP is getting pretty big so I guess that's the only solution. I'll spend a couple hours on it today and see how I feel about it -- I was really holding out hope someone else would come in here with an easier solution.It's just such a simple concept -- see these accounts on this little sheet? remove them wherever you find them on this much bigger sheet. The VLOOKUP calculation is really codey for such a simple concept, seems to me. To anyone reading htis, I'd still love to find a less codey solution.
The data structure on the sheet in my example is not suitable for this task (quickly done just to demonstrate VLOOKUP). The document "sample file" suggested by the participant LeroyG is better suited. Data with transactions should be placed on a separate sheet of the marketing document.