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?
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…
Is there something other than COUNTIF that I might be able to try using a different method? I saw an old message using TRANSPOSE that might possibly work if I knew how to properly use it in this case.
Yes, there are many functions that would solve this problem - COUNTIF() just came to mind first.
=ISERROR(VLOOKUP(A2; C:C; 1; 0))
=ISERROR(MATCH(A2; C:C; 0))
=SUMPRODUCT(A2 = C:C) = 0
Also you can select both lists (if it in one sheet) and choose Format - Conditional, set condition “Cell value is not duplicate” and choose predefined style Error (just for example) - all unique values in column A will be red.
Or you can transfer the data from these two lists to the database, as shown in this video and create a query like
SELECT "Table1"."New List" FROM "Table1" LEFT OUTER JOIN "Table2" ON "Table1"."New List" = "Table2"."Original List" WHERE "Table2"."Original List" IS NULL
I was thinking… if one formula wouldn’t work then likely no formula would work. I thought back to the “text” causing the formula not to work comment and went in and highlighted the entire document and formatted Number to -1234.57 instead of “General” and now your formula works. Thanks for your patience and all your help!