Oct 02 2020 01:22 PM
Not sure how to do this best.
I have 2 works sheets with data arranged in rows (see below).
I'd like to create a Dynamic array formula in a 3rd work sheet that arranges/concatenates all this data in columns per below.
Problem is that the range (n and m) of the data in sheet 1 and 2 is not always the same. I have a macro that generates this data in sheet 1 and 2 and the summary table in sheet 3 should be smart enough to auto arrange the new data extents automatically.
Sheet 1:
Row 1: x1 x2 x3 ... xn
Row 2: y21 y22 y23 ... y2n
Row k: yk1 yk2 yk3 ... ykn
Sheet 2:
Row 1: xn+1 xn+2 xn+3 ... xn+m
Row 2: z21 z22 z23 ... z2m
Row k: zk1 zk2 zk3 ... zkm
Sheet 3:
x1 y21 y31 .... yk1
x2 y22 y32 .... yk2
... ... ... .... ...
xn y2n y3n .... ykn
xn+1 z21 z31 .... zk1
... ... ... .... ...
xn+m z2m z3m .... zkm
Oct 04 2020 03:56 AM
Oct 04 2020 09:23 PM
Essentially to get S3, transpose the S1 and S2 data and then add it into a single table.
The data in S1 and S2 will always have the same number of row entries (Percentiles) and sorted the same way however, the problem is that the number of columns (energies) can vary in both S1 and S2.
Oct 05 2020 01:51 AM
As far as I could understand you would like the S3 to adapt to the variation of the columns in S1 & S2.
This could be done with Power Query, as well as with VBA.
Maybe someone else knows a way with formulas. I am sending you one of the options - Power Query. Below is the handling.
Merge Spreadsheets or Smart Tables with Power Query
Requirements
The most important requirement: You have integrated the Power Query add-in into your Excel and activated it. By default it is not available in Excel 2010/13, but you can download it for free from Microsoft and then install it (Microsoft Power Query for Excel - https://www.microsoft.com/en-US/download/details.aspx?id=39379 .
The installation will automatically activate the add-in.
This functionality is integrated from Excel 2016.
Power Query is an extremely powerful tool for analyzing and evaluating existing data.
The source data does not have to be in a (single) workbook, it can also be different files. The sole requirement for merging the data: The structure of the tables is always the same, which means: Every suitable column in the file to be added must contain data of the same type. The heading must also be identical if the data is to be appended. And then it can be text (as a numerical value) in one data source and a "normal" number in the other, although this is not optimal, but can be remedied afterwards.
Note / addendum: If you want to join several tables / lists together, i.e. join them together in a similar way to copy / paste (which is also required here), the headings of the columns must be identical; otherwise a new column with the corresponding values is added to the generated query. This condition corresponds to the menu item Combine | Append queries. The order is not relevant, Power Query only identifies the columns based on the headings.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)