Delete duplicate data comparing data from another document

asked 2021-04-07 09:57:14 +0200

Ben Shimon gravatar image

The first spreadsheet contains a column of data in "A" that I need compared to column "C" in a different spreadsheet. If data in any cell of column "A" of the first spreadsheet is found to match an entry in any cell in column "C" in the other spreadsheet then I need that entire row removed from the first spreadsheet. I need the data (the entire row) in Column "A" of the first spreadsheet deleted if the same data is present in any cell of column "C" in the other spreadsheet. *I cannot share the documents as they contain sensitive information.

edit retag flag offensive close merge delete


It will be a big problem for you to copy (temporarily) the sheet with column C into this spreadsheet, where is the sheet with column A? This would make it much easier to solve the problem.

JohnSUN gravatar imageJohnSUN ( 2021-04-07 10:28:52 +0200 )edit

So instead of using two documents make the first document have two sheets? That would not be difficult.

Ben Shimon gravatar imageBen Shimon ( 2021-04-07 11:20:45 +0200 )edit

Just a formula to help you accomplish this task will become much easier (shorter). In cell B2 write =COUNTIF(<name_of_second_Sheet> .C:C; A2)=0. Double-click the bold point in the lower-right corner of the cell to multiply the formula to the end of the data. You will receive a lot of TRUE and FALSE. Now just apply the Standard Filter on the second column by TRUE and place the result in the empty space. Auxiliary cells can be deleted. Done - you have a list of items that are missing in the C column of the second sheet. You could do this without copying the sheet, but then the address in the formula would also have to contain the name of the original spreadsheet - this is long and boring

JohnSUN gravatar imageJohnSUN ( 2021-04-07 11:53:12 +0200 )edit

=COUNTIF(<call out=""> .C:C; A2)=0 is what I now see in B2. B3 etc says the same thing except the number at the end increases. I don't see any "TRUE" or "FALSE"

Ben Shimon gravatar imageBen Shimon ( 2021-04-07 12:06:55 +0200 )edit

When I comment it is adding this: ="" which I don't see in the cell. I named the second sheet call out and there is no ="" in the formula I don't know why it says that in my comment.

Ben Shimon gravatar imageBen Shimon ( 2021-04-07 12:18:16 +0200 )edit

Please try formula =COUNTIF('call out'.C:C;A2)=0

JohnSUN gravatar imageJohnSUN ( 2021-04-07 12:30:07 +0200 )edit

Please see this

JohnSUN gravatar imageJohnSUN ( 2021-04-07 12:48:14 +0200 )edit

I am using LibreOffice (x64) if that makes any difference

Ben Shimon gravatar imageBen Shimon ( 2021-04-07 14:02:07 +0200 )edit

No, it doesn't matter - this method works with all versions.

JohnSUN gravatar imageJohnSUN ( 2021-04-07 14:05:58 +0200 )edit

I tried the new formula and same thing - it just shows the formula and in the next cell below instead of 0 at the end it has 1 then the next cell below has 2 at the end instead of 0 or 1 and so forth.

Ben Shimon gravatar imageBen Shimon ( 2021-04-07 14:06:23 +0200 )edit

I just followed the video and did exactly that - when I clicked on the green check mark then it just showed the formula not a 1 or 0 then I clicked on the bottom right corner and it filled in the other cells with the same formula.

Ben Shimon gravatar imageBen Shimon ( 2021-04-07 14:14:23 +0200 )edit

The described behavior is very similar to THIS. Either the mode of displaying formulas is enabled, or for some reason column B is already formatted as text.

JohnSUN gravatar imageJohnSUN ( 2021-04-07 14:51:20 +0200 )edit

I checked all the cells involved and they are formatted for number not text. I checked the View Formula and with the column highlighted I clicked on it and nothing changed. then i did select all and clicked on View and then Formula and nothing changed.

Ben Shimon gravatar imageBen Shimon ( 2021-04-07 15:01:44 +0200 )edit

Very strange... Can you show your file? For example, upload it to Dropbox and populate link to it?

JohnSUN gravatar imageJohnSUN ( 2021-04-07 15:08:49 +0200 )edit

You can use Conditional Formatting to identify the duplicate and delete it manually. I don't know your data volume.

Schiavinatto gravatar imageSchiavinatto ( 2021-04-07 17:14:31 +0200 )edit

Instead of a second sheet, could I just copy the entire column of the other spreadsheet into the first spreadsheet in a new column and then compare the data in one cell ("A2" for example) to that entire new column to see if it matched the the data in any of the cells of the new column. Then compare the data in A3, etc... ? Then filter out all the duplicates. The data is 16 digit numbers in both sheets that I need to identify the duplicates of and remove them from the first sheet. I really didn't think it would be that difficult, but I am new to LibreOffice calc. I can easily do it in Microsoft Office, but the computer I have Microsoft Office on is running Windows 7 and I want to upgrade some of the hardware and install Windows 10 and use LibreOffice.

Ben Shimon gravatar imageBen Shimon ( 2021-04-07 17:38:30 +0200 )edit

Yes, of course, this will make the formula even simpler, without the sheet name. But it worries me that you cannot calculate the formula, you see the text of the formula instead of the value. Say 16-digit numbers? Then, of course, you shouldn't publish this data.

JohnSUN gravatar imageJohnSUN ( 2021-04-07 17:49:28 +0200 )edit

Tried copying the data into the same sheet... still didn't work.

Ben Shimon gravatar imageBen Shimon ( 2021-04-08 09:24:50 +0200 )edit

Sorry, really don't understand. Well, try to create new empty spreadsheet, fill columns A and C with test data manually (few cells) and try to create formula like in video - will it still show formula?

JohnSUN gravatar imageJohnSUN ( 2021-04-08 09:31:53 +0200 )edit

I copied both columns of data into a new spreadsheet and it worked. So now to figure out what might be wrong with my spreadsheets that is preventing the formula from working...

Ben Shimon gravatar imageBen Shimon ( 2021-04-08 17:52:50 +0200 )edit