I have a table of data values to import into a database. One of the columns is called NAME.
I have another table with only one colum: Known values of NAME.
How do I use the second table to delete all rows with a known NAME from the first table…
Failed to get it right despite your instruction…
Import all of them into a temporary table and store something like this as a database view
SELECT T.a, T.b, T.c, Name, T.other_stuff
FROM temp_table as T LEFT JOIN real_table AS R ON T.Name = R.Name
WHERE R.Name IS NULL
If the result looks good:
INSERT INTO real_table (SELECT * FROM my_view)50candidates2.ods (24.7 KB)
Filter column AT by FALSE (not in other list) and copy the resulting table. Hidden rows won’t be copied. Alternatively, filter by TRUE (known by other list), delete all the records below the header row and remove the filter. The remaining list is the list of unknown entries.
Thanks a lot. We’re close, but there are some false falses. For example Aggersborg and Agurkesøen appear in the known list…
See also Request for comments concerning a package of routines made to avoid locked ranges and related issues
My formula references the wrong range.
Yep, just found out. It started with line 17, not 1. verything is hunky-dory now. Thanks a lot 
It happened to me because I referenced the range with the mouse while the second sheet was scrolled down to row 17. This is one of the disadvantages of a spreadsheet. In a database, TableName.Name always refers to all the data in the column named “Name” within the table named “TableName”, not including any column header and without any blank areas below the actual table because there is no empty space below the actual table.
Hello
- in the second sheet create a formula to concatenate all »known names« eg:
="^" & TEXTJOIN("$|^"; 1 ; A:A) & "$"
copy the Formula-result with ctrl+c
- in the first sheet, select the Column in question, and open the Search&replace-dialog
- paste with ctrl+v the formula-result into the search-field, check [x]selection only and [x]regular Expression … click on find all
- close the S&R-Dialog
- right-click somewhere on the Row-headers, and choose delete Rows
I fail to get the first step ‘create a formula’ right.
Checking the TEXTJOIN function it also appears that is limited to 253 strings. I have more than 3000…
I have tried with 10000 entries ( out of one Cellrange ) , and TEXTJOIN works as expected!