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

Comments

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 7.1.1.2 (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