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.
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.
So instead of using two documents make the first document have two sheets? That would not be difficult.
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
=COUNTIF( .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”
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.
Please try formula =COUNTIF('call out'.C:C;A2)=0
I am using LibreOffice 7.1.1.2 (x64) if that makes any difference
No, it doesn’t matter - this method works with all versions.
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.
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.
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.
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.
Very strange… Can you show your file? For example, upload it to Dropbox and populate link to it?
You can use Conditional Formatting to identify the duplicate and delete it manually. I don’t know your data volume.
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.
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.
Tried copying the data into the same sheet… still didn’t work.
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?