Conversion of 'Complex' Google sheet to Excel

Contributor

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

 

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)'"))

 

 

10 Replies

@Castellum812 Flattening date how you did in the GS file is done with PowerQuery in Excel. I've demonstrated it in the attached file. 

Thanks riny,

This is going to be a lot of study.
I'll deep dive tomorrow and see what new question arise

Hi Riny,

Only Just started. Sorry for that
I've achieved good progres but bump into some small issues.

After creating a query PQ generates 'Named Ranges' like 'Table1 or Table _1'
If I decide to delete the query, done it a lot during experimenting the 'Named Ranges' do net get deleted.
Under the 'Name Manger' I can change the names for the ranges but I can not delete them.
Is there a way to get rid of these 'Ranges'?

@Castellum812 Ctrl-click the named ranges you want to get rid of and press the Delete button.

I assume in the 'Name Manager' ?
That does not work either.
If I select multiple ranges the 'Delete' button does change but when I press it to delete nothing happens.

@Castellum812 Named ranges can be deleted via the name manger, but not tables. You can change the table names, though. When you use PQ, but don't want a table for each query you created, set the Load options to "Connection only" and/or "Add to data model". No tables will be created but you can still work with the transformed data in other queries or create pivot tables from them.

 

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

Thanks Riny

By now I had discovered the 'Load to' possibility.
But apparently I had created the table in one of the earliy steps I took.
I now converted it to a standar range and deleted it.

Thanks