Conversion of 'Complex' Google sheet to Excel

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

 

 

3 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