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?
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