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
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Oct 04, 2020
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

Nikolino
I know I don't know anything (Socrates)
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