Dec 14 2021 05:12 AM
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.
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)'"))
Dec 14 2021 06:05 AM
@Castellum812 Flattening date how you did in the GS file is done with PowerQuery in Excel. I've demonstrated it in the attached file.
Dec 14 2021 01:44 PM
Dec 14 2021 08:44 PM
@Castellum812 This would be a good place to start.
https://exceloffthegrid.com/power-query-introduction/
Feb 01 2022 02:05 AM
Feb 01 2022 03:38 AM
@Castellum812 Ctrl-click the named ranges you want to get rid of and press the Delete button.
Feb 07 2022 01:16 AM
Feb 07 2022 02:01 AM
@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.
Feb 07 2022 03:14 AM
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.
Feb 07 2022 03:51 AM