Forum Discussion

swissco67's avatar
swissco67
Copper Contributor
Oct 02, 2020

Dynamic array formulas in Excel 2013

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 i...
Formulas and Functions
swissco67's avatar
swissco67
Copper Contributor
Oct 05, 2020

NikolinoDE,

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.

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Oct 05, 2020

swissco67 

 

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)

 

 

Resources