How to combine multiple ranges (same book, other sheets) into one sheet?

[LobaLuna]

Hi to everybody!

I am using LibO 7.0.2.2 using the package from Lubuntu 20.10 repository.

I have one book with four sheets. The first two have records with 50 columns each. The fist one have 1035 records; the second, 420. The third sheet has the range addressess for each column range in each sheet. I used a vertical layout to have this data:

sheet name |  range address | merge of sheet and address | column name
------------------------------------------------------------------------------
sheet 1    | a3:a1038       | 'sheet 1'.A3:A1038         | temporalmark1
sheet 1    | b3:b1038       | 'sheet 1'.B3:B1038         | disability type
...
sheet 1    | ah3:ah1038     | 'sheet 1'.AH3:AH1038       | disease
sheet 2    | ax3:ax423      | 'sheet 2'.AX3:AX1038       | temporalmark2
sheet 2    | aj3:aj423      | 'sheet 2'.AJ3:AJ423        | disability type 
...
sheet 2    | m3:ah423       | 'sheet 1'.AH3:AH423        | disease

I am creating a fourth sheet where I merge the ranges, column by column. As you can see, the columns ranges do not match columns for each question (column name): temporalmark1 in sheet 1 is in column A and temporalmark2 in sheet 2 is in column AX.

Since I am unable to make DDE function to work in my local Linux computer, I am using matrix formula with INDIRECT function, pointing to a cell where I wrote the range address for each column. Besides, I’m not sure if DDE is my solution…

What I am doing is manually pasting for each range the matrix formula (in a vertical layout). For the first range/question temporalmark1, for example, I select (in sheet 4) 1035 cells, then apply {=INDIRECT("'sheet 3'.B2")}, assuming the above table starts in A1 in sheet 3 and the addresses are in column B…

temporalmark     | disability type  | disease
--------------------------------------------------
data1/sheet 1    | data1/sheet 1    | data1/sheet 1
data2/sheet 1    | data2/sheet 1    | data2/sheet 1
...
data1035/sheet 1 | data1035/sheet 1 | data1035/sheet 1
data1/sheet 2    | data1/sheet 2    | data1/sheet 2
data2/sheet 2    | data2/sheet 2    | data2/sheet 2
...
data420/sheet 2  | data420/sheet 2  | data420/sheet 2

This is a painful method because I have 100 questions (50 in each data sheet).

1. I ask if there is a better method to merge cell ranges, as dragging the matrix formula?
2. Or if the DDE function might help (I cannot make it to work even though I am using a single Calc book)?

I might be convoluting and overcomplicating the solution, but I am unable to do it other way than manually

Any best practice or tip would be VERY MUCH appreciated!

(here is an example of what I am trying to accomplish)
cell-ranges.ods

[mgl]

Hello

Please, edit your question details and append a sample document with fake data sets and expected result.

I am not sure to understand exactly the basis of the merge.

Did you think using Base ?

Kind regards, Michel

[LobaLuna]

Hi, Michael @mgl. Yes, and no. I have thought of using Base but since I am going to analyze data thru R, I just need a CSV file to import into.
The basis of the merge is to consolidate 100 data ranges correspondent to 50 form responses (two ranges per question from two sheets, same book). In order to append a file with fake data, I will take another day, if you do not mind.

[LobaLuna]

Hi, @mgl
I finally compiled an example file. For simplicity, I reduced the number of columns and rows ;D

[mgl]

Hello @LobaLuna

Thank you for your test file. I think I understood your request.

Weacknessees of the actual test sheet
The search vector for the LOOKUP function must be sorted ascending, otherwise the search will not return any usable results. This is presently not the case.
If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion.
In consolidado sheet, the LOOKUP formulae in the columns C to F do not use, in every cell, the same vectors definition but a “moving” definition, which does not take into account the above line. The use of absolute coordinate is recommanded.

Hypothesis
There is no correspondence between the lines, among the sheets.
One line in a sheet concerns one person and this person is not found in another tab.
The columns are sorted differently on the different sheets.
It looks like the sheet are issued from different engines or crew, who do the same analysis but do not provide the same report. The “esay” solution to amend these reports is not considered.

Target
Copy in a new sheet, the data from the other sheets, according to a new order of the columns.

Principles
The simplest way is to build one formula for the copy of data from a specific sheet, for every sheet and to store this in a protected place.
Then each of these columns is to be used one after the other, until blanks appear in column A.

At the bottom the the range odd figures are appearing like this :

Then do the 2nd sheet data copy and paste in B32, like this :

Resulting in :

Then copy the B32:F32 formula and paste it downwards until odd figures appear in column A.

The difference between the first set copy and the second one is that the first copy will use a simple incremental copy, thanks to the fact that the first line where to paste the data is already known : line 2. The line number of the source formulae, which is 20, will be changed to 2, and the column letters will also be adjusted, H being changed in A, during the incremental paste.
The following copy sets location are not known. So the use of a cut and paste protects the line numbers in the formulae, whatever the line where the formulae are pasted.

cell-range-mgl.ods

Summary
1- Prepare the formulae to be copied or cut for the 50 columns and store them in the consolidado sheet.
2- For each analysis report, one copy and paste, then one cut and paste.

More shophisticated method could be used like macro but the simplest the easiest to adapt, maintain…

Hope this helps

---

To show the community that the question has found its answer, please click on the ✓ aside the top of the correct answer and vote by clicking the ^ caret of all usefull answer.

Please do not use Add Answer but edit your original question to enhance the details of your question (answers are reserved for solutions to a problem on this Q&A site). Thanks in advance …

Kind regards, Michel

[LobaLuna]

Thank you very much, @mgl. It is not only an answer, it is a superlecture!!! THANK YOU. I learned good practice. I was making it complex enough and I got lost.