Forum Discussion

Castellum812's avatar
Castellum812
Brass Contributor
Dec 14, 2021

Conversion of 'Complex' Google sheet to Excel

Hi There

 

We are currently switching from Gsuite to Microsoft.

This involves converting current google sheets to Excel.

 

I have a rather complex sheet with quite a bit of Array functions that I would like to convert.

I bump into quite some questions.

sheet.

 

 https://docs.google.com/spreadsheets/d/1sxXYMhBmtTAqr3dRYrTYomCj9zmwY2ausOQfvwYeYfs/edit#gid=1807813718

 

An example of a function below.

 

=ARRAYFORMULA(IF(B1="";;QUERY(FLATTEN(
INDIRECT(B1&"!D3:D")&"|"&
INDIRECT(B1&"!H1:1")&"|"&
OFFSET(INDIRECT(B1&"!H3");;;9^9;9^9));
"where not Col1 starts with '|'")))

 

The function flattens a sheet, the name  is in "B1", into a data table

 

This is done in multiple columns.

They are subsequently combined into one table and after that Split into a new combined overview.

I don't know where to start in excel

 

 

 

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN(B2:O);"|";0;0));
"select Col1,SUM(Col3)
where Col1<>'' 
group by Col1 pivot Col2 label Col1'(Hide this row)'"))

 

 

excel
Formulas and Functions

10 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 07, 2022

    Castellum812 

    PowerQuery is the purpose-written system for such ETL (extract/transform/load) activities associated with Excel.  The Excel formula environment has little in the way of array-reshaping functions but Excel 365 does enable such tasks to be performed from first principles.  In this case

    = LET(
      m,  ROWS(Table1),
      n,  COLUMNS(Table1)-1,
      k,  SEQUENCE(m*n),
      c,  1+MOD(k-1,n),
      r,  1+QUOTIENT(k-1,n),
      dt, INDEX(--date, 1, c+1),
      rsrce, INDEX(Resource,r),
      value, INDEX(Table1,r,c+1),
      unpivotted, CHOOSE({1,2,3},rsrce,dt,value),
      FILTER(unpivotted, NOT(ISBLANK(value)))
   )

    will unpivot the data table and remove blank records.  In the most recent versions of Excel, this can be presented in the form of Lambda functions which wrap the 'nuts and bolts' aspect of the calculation within more meaningful function calls.  It is also possible to deal with higher dimensionality data (nested arrays of arrays) using Lambda functions.  As an alternative, Charles Williams provides an UNPIVOT function within the FastExcel add-in.  This will take data from a cross-tab to normalised form as well as managing headers.

Resources