I have two sheets, each containing a list of words and a list of numbers, associated with the words.
A minimal example would be somethings like this:
Sheet A:
mapping 5
color 7
tree 5
bench 4
why 2
Sheet B:
mapping 6
color 7
plant 5
bench 1
however 5
My goal is to have a sheet which combines both data sets into something like this:
mapping 5 6
color 7 7
tree 5 NONE
bench 4 1
why 2 NONE
plant NONE 5
however NONE 5
The spaces indicate the column breaks.
How can I achieve that?
Welcome!
You can achieve this in several different ways. The method you choose depends on what you mean by “that” What will happen next with this third sheet? Should it respond to data changes in the source sheets? For example, if the row “plant 4” is added to the first sheet, should the row on the third sheet be changed to “plant 4 5”?
And if the row “plant 3” and the row “plant 7” are added to the first sheet, should the line on the third sheet be changed to “plant 14 5”? Or do you need to have three rows with all the values 3, 4 and 7?
Thanks for your reply.
The new sheet can be used independantly from the first ones. So it is more of a copy-paste-problem but as my data is of course bigger than the example I am looking for a more automatic way.
Even more automatic than copy-paste?
Right-click on the second sheet’s tab and select “Duplicate sheet” from the menu
Right click on the letter A (the header of the first column) and “Insert columns after”
Jump to the end of the data. Switch to the first sheet, select the data using Ctrl+* or the “Edit-Select-Select data area” menu. Copy. Go back to the third sheet and paste.
Jump to the beginning of the table and (for convenience) add a header row, for example, “Value Sheet1 Sheet2”
Now use the “Data-Subtotals” command.
Click 2 in the top left to collapse the results.
You can use Edit - Find & Replace to remove the word Result and replace 0 with the word None
You see? Instead of the row “color 7 7” that you expected, you got the rows
color None 7
colour 7 None
This is what I wanted to warn about when I asked clarifying leading questions.
Thank you a lot for the reply with screenshots and explanations!
I tried this and seems to do what I want but unfortunately only for a small amount of data.
My original document has about 8000 rows and in that case, LibreOffice just crashes.
And concerning the colour/color: It was simply a typo in my post which I corrected now.
Oops! Please tell me more about this. This should not happen - I assure you, even 80 or 800 thousand rows should not create problems! What version of LibreOffice do you have installed, under what operating system, on what computer? At what step does the failure occur?
I have Version 7.3.7.2 on Linux Ubuntu.
When I press the OK-Button in the last step something seems to be happening for some seconds and then I get the report that the program crashed and that I can either “wait” or “force quit” - and waiting just leads to the same message.
This is strange behavior. Okay, are the first steps of creating a summary sheet going smoothly? Please try executing “Insert - Pivot Table” instead of the “Data-Subtotals” command. Will this operation complete successfully?
sounds like this one :
and to complete the statement databases do this naturally, you can look there :
https://help.libreoffice.org/7.6/en-US/text/sdatabase/02010101.html
