Ask Your Question

Remove duplicates -- "filter" is hard to understand

asked 2020-10-15 23:03:06 +0200

default_abuser gravatar image

updated 2020-10-30 16:44:32 +0200

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.


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:

edit retag flag offensive close merge delete



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!

newbie-02 gravatar imagenewbie-02 ( 2020-10-23 00:02:25 +0200 )edit

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 gravatar imagedefault_abuser ( 2020-10-23 00:50:28 +0200 )edit

@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 ...

newbie-02 gravatar imagenewbie-02 ( 2020-10-23 14:14:40 +0200 )edit

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.

default_abuser gravatar imagedefault_abuser ( 2020-10-23 18:20:34 +0200 )edit

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.

Frants gravatar imageFrants ( 2020-10-23 20:10:56 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2020-10-16 11:07:09 +0200

Frants gravatar image

updated 2020-10-16 12:01:31 +0200

It might be worth trying to change the method of data processing. You have two lists, Customers and Marketing. Add a calculated field to the Customers list with a VLOOKUP function that looks up the account number in the Marketing list. For customers who are not in the Marketing list, the function will return the # N / A result. After that, you can sort the range of the Clients list by this column. Customers with a transaction will be at the top of the list. An exemplary form of the formula = VLOOKUP (A2; E: F; 1; 0) For example C:\fakepath\Clients_and_transaction.ods Best regards.

edit flag offensive delete link more


I've never done anything like that. I looked at your sheet; I understand the concept; I do not understand one iota "=VLOOKUP(A22,E:F,1,0)". I've never used a comma in a calculation before lol and I have no idea what it "means". I trust your suggestion does do what I'm asking for -- I trust that you have offered a valid solution -- but I'm not sure how far I'd have to backup from your suggestion to have the foundation to even begin to implement your idea. And that's pretty UGH when I'm trying to accomplish something I've done for literally years, have always thought of as simple and straight forward.

default_abuser gravatar imagedefault_abuser ( 2020-10-16 20:25:10 +0200 )edit

I am sorry that my answer did not help you. Still, let me say something. When I look at the formula in my document, I see that =VLOOKUP(A22; E: F; 1; 0). There is no comma here. Maybe it's because of the national settings of the application. I have them for the Russian region. Either way, the formula contains parameters and they are separated (in your case) by a comma. You can refer to the application help and read more about it (maybe the separators need to be corrected according to your region). In my example, this function does the following. Takes a value from A22 (customer account number) and searches for it in the range delimited by columns E and F (list of transactions). The search is performed in the first (parameter value is 1) column of this range (customer account with transaction). A parameter with a ...(more)

Frants gravatar imageFrants ( 2020-10-17 18:37:06 +0200 )edit

But, you can use this method for a while in parallel with your usual method.

Frants gravatar imageFrants ( 2020-10-17 18:40:02 +0200 )edit

My boss won't have much tolerance for mistakes I make ramping up the learning curve--it's the sort of penalty that makes learning new things not worth it. I don't think they realize that LO lacks features that Office 2010 had (sort by color/format) and will not be forgiving of errors I make as I ramp up the learning curve on VLOOKUP. I can understand their confusion/frustration; why would/does LO still lack features that were in Excel 10 years ago (I no longer have access to Excel) (and have been requested by LO users for 6 years?)? They may realize it's not my fault but will not have anyone else to blame, either. I may or may not experiment with VLOOKUP it in my personal time, but in the meantime at work I'll just be searching for and manually deleting the 200 lines ...(more)

default_abuser gravatar imagedefault_abuser ( 2020-10-19 21:31:25 +0200 )edit

Your position is quite understandable. If the proposed solution does not inspire confidence and cannot be considered reliable, then it could help solve at least part of the problem (the rest of it manually). Can we think about this task together with you? Can you link to an example of your document with at least a couple of lines of fictional data? It would help to understand the data structure and think. There is another option, use the Base application. The SQL query tool is very effective. Best regards!

Frants gravatar imageFrants ( 2020-10-20 09:36:03 +0200 )edit

I would love to think through this task together. I can not however figure out how to upload a file and link to it as you did. Also I'm not sure how to create a fake document that gets the point across since the real documents are troves of real sensitive customer data. Two spreadsheets, each several columns of customer data, each row a new customer. I just copy the entire column of account numbers from the sales sheet, paste all those account numbers at the bottom of the column of account numbers on the marketing sheet, then highlight the dupes. Now all the customers who have made a transaction are highlighted so I can remove them. Then on the marketing sheet I remove the account numbers I had (temporarily) pasted from the Sales sheet, and presto -- clean marketing sheet containing only accounts that are not on the sales ...(more)

default_abuser gravatar imagedefault_abuser ( 2020-10-22 22:35:14 +0200 )edit

Before that, prepare fake documents.

  1. Make copies of your original documents and keep them in a safe place.

  2. Create a folder for fake documents.

  3. Save each of the documents with a new name (Save As) in this folder. The name should be very different so as not to confuse you in the future. For example, TempClients123, TempMarketing456.

  4. Open each of the fake documents and remove all data lines from it. Correct the column names, if they contain any confidential data. And, make sure filtering is disabled. Leave only rows with column headings.

  5. Look again at all Sheets in the fake documents. Only the column names should remain.

  6. Close the application with documents.

  7. Do Upload as described below.

Frants gravatar imageFrants ( 2020-10-23 07:25:28 +0200 )edit

@Frants, Answers are not intended for comments or adding information.

Every user can upload ODF files to his/her question. See

LeroyG gravatar imageLeroyG ( 2020-10-23 17:59:08 +0200 )edit

Thanks! There is a lot written there, but I could not figure it out, sorry. Just help, give step-by-step instructions on what to do to the user to upload documents. Best regards! I think I'm starting to understand ...

Frants gravatar imageFrants ( 2020-10-23 18:06:27 +0200 )edit

To upload your documents:

In the form of your question, click the Edit button. Buttons will appear above the form, click the "attachment" button. A dialog box will appear to select a file. Repeat this for each document.

Don't forget to click Save.

Frants gravatar imageFrants ( 2020-10-23 18:14:54 +0200 )edit

answered 2020-10-22 22:18:49 +0200

LeroyG gravatar image

One sheet for the marketing list. Other sheet for the transactions. A VLOOKUP formula near the marketing list echo the account number if the same account number is at the transaction sheet.

You can sort VLOOKUP and marketing list columns and delete all rows that show numbers for VLOOKUP.

No need to copy from transaction list to marketing list. Only check that VLOOKUP column has the formula.

See the sample file.

Made with LibreOffice (x86); OS: Windows 6.1.

Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

Check the mark (Correct answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more


=IFERROR(VLOOKUP(B3,$transaction.A:A,1,0),"--") that is so beyond my paygrade friend, like BIG lol -- as I said elsewhere, I've never used a comma in a calculation, just to begin wtih. I think i'll sooner hunt and peck for the lines that have been highlighted by Conditional Formatting. It's just very hard from my competency level to gauge how much time and effort I will need to put in to get to a competency level where that calculation is sensible. I'm kinda thinking that just doing it the hard way will save me more time than learning how to do that, but really how can I know, but best guess. I mean...that just looks crazy, I can't begin to make sense of it this moment. Sorry. As I said to the other person, I trust that this is a valid solution ...(more)

default_abuser gravatar imagedefault_abuser ( 2020-10-23 00:55:53 +0200 )edit

@default_abuser, Just share a file with one row with fictitious data. I adapt the formula, and you only need to copy and paste the formula in the right place in a copy of your document to give it a try.

LeroyG gravatar imageLeroyG ( 2020-10-23 02:48:53 +0200 )edit

I had to remove the green Answer check due to this issue of lag. The lag is so substantial it often locks up Calc for a minute, sometimes 2, and I'm really afraid it's going to corrupt the file. THis is absolutely NOT a reasonable solution given this. Sorting a column that contains calculations seems to kill LOCalc.

default_abuser gravatar imagedefault_abuser ( 2020-10-30 16:39:31 +0200 )edit

@default_abuser, But it wasn't my answer that had the green check mark. Again, just share a file with one row with fictitious data. With my proposed answer the lag is negligible.

LeroyG gravatar imageLeroyG ( 2020-10-30 18:55:16 +0200 )edit

Indeed, sorting a calculated field in a large data set is inefficient. This only gives rise to further reflection. 1. In my opinion, the best solution would be to use a database. Large arrays, selections, and sorting are the power of the database. 2. After performing VLOOKUP calculations, you can disable the automatic recalculation mechanism. Choose Data-> Calculate-> AutoCalculate (uncheck). Then sort. 3. Maybe sorting is unnecessary? Maybe a VLOOKUP filter is enough?

Frants gravatar imageFrants ( 2020-10-30 19:36:45 +0200 )edit

answered 2020-10-16 11:52:20 +0200

kompilainenn gravatar image
edit flag offensive delete link more


I will try that. One concern I have is that sometimes I want to remove both instances of duplicates, and occasionally I just want to remove the duplicate and leave behind one instance. Sometimes I want to do some of each in the same document/process! The method I described above in Excel allows you to review all the "dupes" -- as they have been highlighted/formatted by conditional format, and then sorted by their format/highlight so they're all in one place -- and then I can assess them and process them in batches, deleting both instances of some and deleting only the dupe on others.

default_abuser gravatar imagedefault_abuser ( 2020-10-16 20:24:03 +0200 )edit

That didn't work. Again, Conditional Formatting finds several more dupes than this extension does. I took a sheet of 113 lines, 7 of which are/have duplicates according to Conditional Formatting (and verified by me). When I use the Remove Duplicates extension, it reports "We found and deleted 2 duplicated values. Now we have only 111 unique values" which is just wrong -- there are still 4 lines that are/have dupes in the list. The dupes are still highlighted by conditional formatting! That's a really, really, really big error rate (greater than 50%!).

edit: It does find all 7 if I have it analyze only column A. If I have it analyze Columns A-E, it only finds 4 dupes. Weird. But I guess it works to some degree. Still, it doesn't accomplish what I need which is the ability to identify dupes and organize them together for ...(plus)

default_abuser gravatar imagedefault_abuser ( 2020-10-16 20:32:33 +0200 )edit

also, this plugin only removes the dupe, not ALSO the original instance. More often than not, I want both instances removed. Only occasionally do I want to leave behind one instance as this extension does by default and it doesn't appear to be an option the user can adjust.

default_abuser gravatar imagedefault_abuser ( 2020-10-16 20:56:10 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2020-10-15 23:03:06 +0200

Seen: 364 times

Last updated: Oct 30 '20