I have 3 spreadsheets with test data on 17 parameters for 100 gizmos. Each sheet is for 1 year. The headers are common. I want to merge all sheets without performing any function on the data so that a trend can be seen on how data has moved for each parameter over 3 years. Once I have this master file ready, then I should be able to use any three sheets for different gizmos to generate a summary sheet. Thanks for reading.
This thread in a traditional forum may help you: Apache OpenOffice Community Forum - [Solved] Read data from a series of external spreadsheet fil - (View topic) .
Otherwise:
The subject of the question is about files. The text is talking of 3 spreadsheets which you want to “merge”.
Please edit your question to make the situation more clear.
BTW: “Gizmo” isn’t a well known English term. (This is an international community using English as their language of undertstanding.)
If the question actually is about just three spreadsheets, each one in an extra file:
open the three files and create a forth empty Calc document.
In everyone of the three source documents select the sheets tab and uise the context menu with >Move or copy sheet... and >>To document to get copies of the sheets into the new target document.
This done copy the data rows from the second and the third sheet and paste them into the first sheet below the other data.
1 Like
Thanks for your response.
I am new to this forum - will try and be more clear as I go on.
Rather than trying to clarify my question through text, let me just attach a file.
Sample Trend Sheet.xlsx (10.4 KB)
I have 100’s of files with data for the past three years for many parameters - the sample sheet has 17 parameters as an example. I want to automate the process of creation of a summary trend sheet as shown in the 4th tab of the above file.
In other words, you need to parse each of the three tables into separate rows and create a long plaine table and then apply a PivotTable to it?
Well, in principle this can be done in several ways. But please tell me, your files are stored in xlsx format - does this mean that you have Excel? And if so, do you know how to use the tools that are on the Data tab? The fact is that for more than 10 years there has been a tool that was originally called PowerQuery (then it was renamed Transfer&Go, then it was completely deprived of its independent name, but that’s not what we’re talking about now).
For this tool, the “unpivot” operation is built-in. And combining data from different sheets or from different books is also built-in. Perhaps you should look in that direction? Unfortunately, I can’t help with this; I haven’t used Excel for a long time. But perhaps a few thematic videos on YouTube can help you with this?
Welcome to LO!
I think you have 2 basic directions to consider:
a) use cell formulas and sheet linkages in Calc …
b) import data to Base and assemble your desired table/s there - then export results to spreadsheet as you need…
Either will take time, but the database solution is much more scaleable and modifiable in the long run, whereas heavily customised spreadsheets can be very fragile…
Good Luck!