Wow, thanks for going the extra mile and providing an example. I will look into it, might be what I needed.
As for the “B” option, it’s no good, there are about 3k entries and it needs to be automated.
Automation may require a bit of progranning user code. I will not encourage you to go this way, but if you do not feel the need to migrate your application to a database anyway, this is also an option in the specific case. I personally prefer solutions by formulae what is the “Spreadsheet-Do” IMO. However we have to consider formula solutions to get ineffective when applied to very large data collections.
Isn’t there a way to block multiple entry of keys from the beginning?
Sorry! The announced “second attachment” was not accepted by the askbot for unknown reasons. I will try again later.
As you see it finally worked after a few ‘Refreshs’ in my browser. This askbot really adds some additional waste of time.
Ok, so I tested your spreadsheet with a small portion of my data, it seems to work very well. Although I don’t yet know why and how 
(I have to run)
Though, my spreadsheet is not meant to be infinite. Data fields have a fixed size, but I’m starting to wonder if it could in fact be turned into a continuous table…
The very foundation of my spreadsheet is to enter those values in these two columns with a random pattern. They are supposed to repeat.
For information, in this case it is no necessary to filter MERGER Does not begin with... ZzZzZz%.
In the field name just select - none - and of course, don’t forget to tick Options▸No duplications
Regards
(You made me flush. I am just after the karma.)
@Aigars: Yes (if I understand correctly what ranges you want to fix - and relative addressing is included with the term). But what good for. Searching long columns for matches using MATCH with order-parameter 0 (no sort order assured) may soon get inefficient. In addition I regard it bad style to search longer parts of columns as actually needed. You also may get fake errors in (the rare) case of having “fixed” mor than a half of the available column…
I regard OFFSET to be the means of choice anyway for engines of the kind. It offers a (next to) transparent way to acces arbitrary parts of remote (rectangular) ‘SheetCellRange’(s). To get the functionality using INDEX would require to pass calculated addresses in addition to the very thorough usage of absolute and relative addressing.
I ran same test with this code and it works perfectly.
SELECT
"Column1"
FROM
"_TABLE_"
WHERE
"colum1"
NOT IN(
SELECT
"Column2"
FROM
"_TABLE_"
) UNION
SELECT
"Column2"
FROM
"_TABLE_"
WHERE
"Column2"
NOT IN(
SELECT
"Column1"
FROM
"_TABLE_"
)This looks like SQL, but I’m working with a spreadsheet in calc.
@Aigars: then it might not be possible without complex cell code or a macro. I seem to recall SQL can be used in Calc too.
You can register the Calc file as a database and then use that SQL code.
It’s starting to sound promising, but I lack the knowledge in this area.
Something can be done with Menu/Data/Consolidate, but there is a bug and only the first range to consolidate it’s preserved in the option when the file it’s saved.
Worked fine for me, with no bugs, but I was looking for a more or less automated solution.