Dynamic array formulas in Excel 2013

New Contributor

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


3 Replies
Sorry, but your description does not match the inserted file.
I am not the best example in terms of understanding :), but with your permission.
I would recommend that you adapt the description and the example exactly to your project.
So that an ignorant person as I can understand it.
I'm sure you could come to a faster and more tailored solution than has been the case up to now.

Thank you for your understanding and patience

I know I don't know anything (Socrates)


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.



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
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.



I know I don't know anything (Socrates)