Ask Your Question

Get the substraction from two set of names

asked 2020-04-01 04:10:44 +0200

marionline gravatar image

In libreoffice calc I have one column with a set of names, example:

Column A: Car, Motor, Egg, Apple, Pig

And a row with other names:

Row 1: Car, Motor, Apple

I want to get in one cell the difference between the two sets (the result should be "Egg, Pig"). Is it possible?

edit retag flag offensive close merge delete


Many things are possible, but may not be reasonable.
This is a case where an assurance cocerning the "size" is essentilal: It may be feasible and (less likely) even reasonable to return the result to one cell. It shouldn't be reasonable, however, if you have to expect 789 (e.g.) results to be concatenated for the display of one cell.
Please describe your use-case to more detail, and assure the maximun numbers for the relevant three countables:
Sequence of items in the original column
Sequenc of items to be excluded (Really in a row?)
Sequence of remaining items (complement).
Please also tell what use you intend to make of the result. Do you want to "just look at it" or shall further automated processing take place?

Lupp gravatar imageLupp ( 2020-04-01 11:01:20 +0200 )edit

It want an automated process. In more detail, I have a set of row (not column) with different names: Row 1: Car, Motor, Apple Row 2: Car, Motor, Egg Row 3: Pig, Motor, Egg Row 4: etc

I have a column with total names without repetitions (Car, Motor, Egg, Apple, Pig, less than 20 item). For each row I want to write into a single cell the item not present in the row (example: row1 "Egg, Pig", row2 "Apple, Pig", row3 "Car, Apple").

Hope this can help, thank you.

ADDENDUM It is like an sql statement logically similar to SELECT name FROM column WHERE name NOT IN rowX

marionline gravatar imagemarionline ( 2020-04-01 11:32:54 +0200 )edit

"For each row I want to write into a single cell the item not present in the row". You surely dropped the plural "s" inadvertently.
I understood you well insofar. But I'm afraid you didn't take my comments seriously.
Your SQL statement would deliver a SEQUENCE of the actual length and take whatever needed space for it. A single cell cannot return a sequence in the full sense. It needs to get the sequence converted to a list.
Spreadsheets simply aren't made for the kind of task, and your special requirement of "result to one single cell per row" is an relevant aggravatrion.
A serious issue in spreadsheets, however, is the SIZE of the task which is much less relevant for databases. "Size-up to the real thing" is the issue.
You should better have answered my questions instead of saying the same things again with slightly different ...(more)

Lupp gravatar imageLupp ( 2020-04-01 12:08:20 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-04-01 12:12:43 +0200

Lupp gravatar image

I wouldn't wait longer for answers on my questions in return.
Study this attached example (was:ask236275anothrSELECT_WHERE_ByInadequateMeans_1.ods).

edit flag offensive delete link more


Thank you, this is what I need!

marionline gravatar imagemarionline ( 2020-04-01 13:45:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-04-01 04:10:44 +0200

Seen: 23 times

Last updated: Apr 01