How to find values present in different sheets and copy them in Calc?

[Seneika]

Hi, I’m new to Calc and I’d appreciate some help with the following.

I have a document with different sheets containing lists of similar data. I’d like to do something like:

For all values in Sheet1 column A if they are present also in Sheet2 column B then copy them to the current column.

How can I do this? Thanks.

EDIT:

How about the following modification?

For all values in Sheet1 column A and Sheet2 column B remove the duplicates and copy the results to the current column (or, alternatively, to Sheet3 column C).

[Lupp]

Did you want to assure that any of the columns A, B does not contain repeated values in itself?
Bascally you may think of A and B as of sets. (No duplicates allowed!)
Then the first task aims at Intersection (A , B)
and the second one at Complement( Union( A , B ) , Intersection( A , B ) ).
If A and/or B are lists or sequences probably containing repetitions, you may first have to create sets of them.
In short: What do you need that for?

[JohnSUN]

For your first question: you can try to use Advanced Filter

For your additional question: if I correctly understood you then you need to enable the option No Duplications in the same dialog

Edit: Perhaps this video instruction will help you to start

Also you can see this movie in Youtube

[jimk]

I tried this but encountered an error, “This range does not contain a valid query.”

[JohnSUN]

OK, I supplemented my answer - I hope that now you will succeed

[jimk]

It works now with the addition of the “Name” header above each list. Although the new information is helpful, animated gifs are painful and difficult to watch. Painful because of sensory overload (like the disgraced tag in HTML), and difficult because there’s no pause button. The important new information could be shown in a single still image.

[JohnSUN]

Yes, you are right - GIF was a bad idea. But this resource cannot display any other format of movie. May be this variant will be better. So, you say that now it works? Well, you can mark answer as correct

[jimk]

The YouTube video is much better than the GIF.

[jimk]

Are you asking me to mark it as correct rather than the OP? There are three solutions proposed here, all of which are valid, and I do not know which is best for the OP’s specific needs. However, I will +1 because it is simpler than the others.

[JohnSUN]

Oh, sorry, my friend, I thought it was your question. I’m sorry! Maybe my head was at the celebration Thanks for your vote. I will add a link to Youtube in my answer

[jimk]

While it would be possible to devise an awkward solution with Calc using VLOOKUP, this is a typical problem to solve in Base. First, go to File-> New → Database. Hit Next, No, do not register the database and then Finish. Save the file.

Back in Calc, select the data from Sheet 1 column A and drag to the Base window where it says “Tables” to create Table1. For more information on these options, see my answer at https://superuser.com/a/1216718/541756. Do the same for Sheet 2 column B, creating Table2.

Now in the queries section, the first part of your question can be solved by creating the following query in SQL view. (Actually, I created this using Design view, but this way you can copy and paste from the answer).

SELECT "Table1"."VAL" FROM "Table2", "Table1"
WHERE "Table2"."VAL" = "Table1"."VAL"
ORDER BY "Table1"."VAL" ASC

The answer to the second part uses a more complex query, based on sql - How to select all records from one table that do not exist in another table? - Stack Overflow.

SELECT t1."VAL" FROM "Table1" t1
LEFT JOIN "Table2" t2 ON t2."VAL" = t1."VAL"
WHERE t2."VAL" IS NULL
UNION
SELECT t2."VAL" FROM "Table2" t2
LEFT JOIN "Table1" t1 ON t2."VAL" = t1."VAL"
WHERE t1."VAL" IS NULL

Alternatively, use this shorter query for the second part.

SELECT "VAL" FROM (
    SELECT "VAL" FROM "Table1"
    UNION ALL
    SELECT "VAL" FROM "Table2")
GROUP BY "VAL"
HAVING COUNT("VAL") = 1

The results of the queries can optionally be moved back to the Calc spreadsheet by clicking and dragging.

Example files: Untitled 1.ods, New Database.odb

[Lupp]

No specific mentioning of “different sheets” below, as this aspect is not relevant for formula based solutions.

Set operations in Calc

The question is partly identical with many others posted once in a while in this forum and in forum.openoffice.org, but also in the regrettably late libreofficeforum.org.
Interactive solutions and solutions based on database usage were often suggested, and solutions in Calc by formulae based exclusively on standard functions, mostly depending on helper columns, are also around.

For small (say N <= 1000 e.g.) numbers of datasets and in cases where general advantages of databases ar not relevant, I would also promote an approach mainly based on the recently (V5.2.2) implemented, and since V5.3.3 actually usable new function TEXTJOIN(). It’s a pity that neither Excel that induced the enhancement nor LibO also have implemented a corresponding TEXTSPLIT() function, because the pair would be what makes standardised solutions for tasks of the kind discussed here easy. Fortunately, on the other hand, the implementation of the missing function by custom code is extremely simple since BASIC has the Split function which is next to all we need.

Thus I made a Calc document containing the 10 lines of code creating a comfortable TEXTSPLIT, and a few demos how to use it in combination with TEXTJOIN (and often with TRANSPOSE) to solve the basic tasks of set manipulation.

Enjoy this demo, but stay aware of the fact that the size of the tasks will influence the efficiency of solutions by a higher order than 1 (linear). Basically time consumption should be of order O(N^2).

The demo does not work in AOO as it lacks the TEXTJOIN function.

Comments and criticism welcome.

[jimk]

Could you provide a breakdown explanation of how one of the formulas works, for example =TRANSPOSE(TEXTSPLIT($A$1;TEXTJOIN($A$1;1;IF(IFERROR(MATCH(B3:B1002;B3:B1002;0)=ROW(B3:B1002)-ROW(B$3)+1;0);B3:B1002;""));1000)). I am currently trying to understand the role of MATCH in this formula.

[Lupp]

If you give a set in the form of a list there may be repetitions, and in the examples there actually are. Looking at column B you find a sequence of 12 items e.g. while the set of these items only has 9 elements. For better clarity we prefer to enumerate the elements of finite sets without repetitions. Column E therefore shall only list the 9 distinct items. To exclude the repetions, positions containing items that are not the first match for themselves in that place, are replaced with “”.

[Lupp]

The usage of MATCH in column O is significantly different. It is not needed to exclude repetitions, but to exclude the elements of the set for which the complement relative to column M is to compute.
BTW: I have coded a more powerful xTextjoin() capable of excluding repetions on the fly based on a parameter value. It would allow for much simpler formulae. Due to the laziness of BASIC it is much less efficient, of course.