Here are some examples of Excel Office 365 Formula from beginner through to advanced using the LAMBDA() function. We will use Dynamic Named Ranges with all of these Formula. Here is a link to the Workbook containing these examples Office365.xlsx
To use the Formula examples, create a Named Range in your Workbook matching the example (1 or more Columns wide) and then highlight the Formula within the box boundries using the Mouse. Press CTRL+C to copy to the Clipboard and adjust accordingly. Please Note: you can refer to a Spill Array by using a '#' (hash) suffix and then use this in another Formula
UNIQUE · SORT · SORTBY · HSTACK · FILTER · TAKE · CHOOSECOLS · TOROW · SEQUENCE · GENERATING COMBINATIONS & LAMBDA (2 Lists) · GENERATING COMBINATIONS & LAMBDA (3 Lists) · GENERATING COMBINATIONS & LAMBDA (4 Lists) · TRANSPOSE CROSS TABLE TO LIST · TRANSPOSE CROSS TABLE TO LIST & LAMBDA
UNIQUE()
array - Range or array from which to extract unique values
by_col - [optional] How to compare and extract. By row = FALSE (default); by column = TRUE
exactly_once - [optional] TRUE = values that occur once, FALSE= all unique values (default)
With any Named Range you can use the UNIQUE() function to Spill the Array, for example, consider the dynamic Named Range 'SKUList' in the example Workbook (values can be text, numbers, dates, times, etc.). You can also combine this with the INDEX() function to grab a specific Column in the Named Range:
' Syntax
=UNIQUE(array, [by_col], [exactly_once])
' will output the unique SKUS for the Named Range 'SKUList' (1 Column array)
=UNIQUE(SKUList)
' will output a unique list of Plant Descriptions for the Named Range 'PlantList' (2 Column array)
=INDEX(UNIQUE(PlantList),,2)
SORT()
array - Range or array to sort
sort_index - [optional] Column index to use for sorting. Default is 1
sort_order - [optional] 1 = Ascending, -1 = Descending. Default is ascending order
by_col - [optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE
With any Named Range you can use the SORT() function to Spill the Array, for example, consider the dynamic Named Range 'SKUList' in the example Workbook (values can be text, numbers, dates, times, etc.):
' Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
' will sort the unique SKUS Ascending for the Named Range 'SKUList' (1 Column array)
=SORT(UNIQUE(SKUList))
' will sort the unique SKUS Descending for the Named Range 'SKUList' (1 Column array)
=SORT(UNIQUE(SKUList),,-1)
' will sort the unique Plants by the Plant Description Column (2) Ascending for the Named Range 'PlantList' (2 Column array)
=SORT(UNIQUE(PlantList),2,1)
' will output a unique, sorted list of Plant Descriptions Column (2) Ascending for the Named Range 'PlantList' (2 Column array)
=SORT(INDEX(UNIQUE(PlantList),,2),,1)
SORTBY()
array - Range or array to sort
by_array - Range or array to sort by
sort_order - [optional] Sort order. 1 = ascending (default), -1 = descending
array/order - [optional] Additional array and sort order pairs
With any Named Range you can use the SORTBY() function to perform Multiple-level Sorts and Spill the Array, for example, consider the dynamic Named Range 'Range1' in the example Workbook (values can be text, numbers, dates, times, etc.):
' Syntax
=SORTBY(array, by_array, [sort_order], [array/order], ...)
' Sort Named Range by PROD (Column 1) Ascending
=SORTBY(Range1,INDEX(Range1,,1))
' Sort Named Range by PROD (Column 1) Ascending and then by FCST (Column 3) Descending
' - explicit set Ascending and Descending for brevity
=SORTBY(Range1,INDEX(Range1,,1),1,INDEX(Range1,,3),-1)
' Sort Named Range by PROD (Column 1) Ascending, then by FCST (Column 3) Descending and then Filter by VAR (Column 4) anything > 100%
' - here you have to repeat the SORTBY() function and wrap the results in a INDEX() for the VAR Column in order to use the FILTER() function
=FILTER(SORTBY(Range1,INDEX(Range1,,1),1,INDEX(Range1,,3),-1),INDEX(SORTBY(Range1,INDEX(Range1,,1),1,INDEX(Range1,,3),-1),,4)>1)
' Lastly return the Top 3 Results
' Sort Named Range by PROD (Column 1) Ascending, then by FCST (Column 3) Descending and then Filter by VAR (Column 4) anything >100%
=FILTER(SORTBY(Range1,INDEX(Range1,,1),1,INDEX(Range1,,3),-1),INDEX(SORTBY(Range1,INDEX(Range1,,1),1,INDEX(Range1,,3),-1),,4)>=LARGE(INDEX(SORTBY(Range1,INDEX(Range1,,1),1,INDEX(Range1,,3),-1),,4),3))
' Considering Range2 for a list of Fruit with Qty. and Price
' will Sort Named Range by FRUIT (Column 1) Ascending and then by QTY. (Column 2) Descending
=SORTBY(Range2,INDEX(Range2,,1),1,INDEX(Range2,,2),-1 )
HSTACK()
array1, array2, array3 etc. - Range or array to filter
With any Named Ranges you can use the HSTACK() function to Spill the Array arguments in a column-wise fashion, for example, consider the dynamic Named Ranges 'SKUList' and 'PlantList' in the example Workbook (values can be text, numbers, dates, times, etc.):
' Syntax
=HSTACK(array1, array2, array3)
' will append the two arrays next to each other by Column (result is 3 Columns)
=HSTACK(SKUList,PlantList)
' will append the two arrays next to each other by Column but omit the Plant Descriptions (2 Columns)
=HSTACK(SKUList,INDEX(PlantList,,1))
FILTER()
array - Range or array to filter
include - Boolean array, supplied as criteria
if_empty - [optional] Value to return when no results are returned
With any Named Range you can use the FILTER() function to Spill the Array, for example, consider the dynamic Named Range 'PlantList' in the example Workbook (values can be text, numbers, dates, times, etc.). Use a '*' symbol for AND, use a '+' symbol for OR. You can combine this function with ISNUMBER(), SEARCH() and FIND() to perform Fuzzy Matching or Case Sensitive Fuzzy Matching:
' Syntax
=FILTER(array, include, [if_empty])
' Exclude Plymouth Brewery from the Plant List array Description (Column 2) using Filter
=UNIQUE(FILTER(PlantList,INDEX(PlantList,,2)<>"Plymouth Brewery"))
' Exclude Devon Brewery AND Plymouth Brewery from the Plant List array Description (Column 2) using Filter
=UNIQUE(FILTER(PlantList,(INDEX(PlantList,,2)<>"Devon Brewery")*(INDEX(PlantList,,2)<>"Plymouth Brewery")))
' Include Devon Brewery OR Plymouth Brewery from the Plant List array Description (Column 2) using Filter
=UNIQUE(FILTER(PlantList,(INDEX(PlantList,,2)="Devon Brewery")+(INDEX(PlantList,,2)="Plymouth Brewery")))
' Filtering the Plant List using SEARCH Fuzzy Matching "*No*" for the Description Column
=UNIQUE(FILTER(PlantList,ISNUMBER(SEARCH("*No*",INDEX(PlantList,,2)))))
' Filtering the Plant List using FIND Case Sensitive Matching "De" for the Description Column
=UNIQUE(FILTER(PlantList,ISNUMBER(FIND("De",INDEX(PlantList,,2)))))
' Stack SKUList with PlantList Code (Column 1) next to each other and Filter by a specific SKU "A1002"
=FILTER(HSTACK(SKUList,INDEX(PlantList,,1)),INDEX(SKUList,,1)="A1002")
' Using a Dynamic Named Range ExampleData1 to pick up all of the data except the Headers
' - filter the entire Range by Key Figure (Column 1), "Final Forecast"
=FILTER(ExampleData1,INDEX(ExampleData1,,1)="Final Forecast")
' All of the Data by Key Figure (Column 1), "Final Forecast" AND SKU (Column 3), "A1002"
=FILTER(ExampleData1,(INDEX(ExampleData1,,1)="Final Forecast")*(INDEX(ExampleData1,,3)="A1002"))
TAKE()
array - the source array or range
rows - [optional] the number of rows to return. A positive value takes rows from the start of the array and a negative value from the end of the array. If omitted, columns must be set
columns - [optional] the number of columns to return. A positive integer takes columns from the start of the array and a negative integer from the end of the array. If omitted, rows must be defined
With any Named Range you can use the TAKE() function to extracts the specified number of contiguous rows and/or columns from the array or range:
' Syntax
=TAKE(array, rows, [columns])
' Grab the first 2 Rows exactly 4 Contiguous Columns wide from a Named Range
=TAKE(ExampleData1,2,4)
' Grab the last 2 Rows exactly 4 Contiguous Columns wide from a Named Range
=TAKE(ExampleData1,-2,4)
' Grab a Unique List of the Key Figures (Column 1) from a Named Range
=UNIQUE(TAKE(ExampleData1,,1))
CHOOSECOLS()
Array (required) - the source array or range
Col_num1 (required) - an integer specifying the first column to return
Col_num2, … (optional) - index numbers of additional columns to return
With any Named Range you can use the CHOOSECOLS() function to return the specified non-contiguous columns from an array or range. You can use a horizontal array constant such as {2,3,4} or a vertical array constant such as {2;3;4} to specify the column numbers. You can bring back all Odd Columns or all Even Columns using SEQUENCE() with ROUNDUP() and ROUNDDOWN(). For these examples we will use a Dynamic Named Range called ExampleData1:
' Syntax
=CHOOSECOLS(array, col_num1, [col_num2], …)
' Grab 5 Non-Contiguous Columns from a Named Range and Output in any Order
=CHOOSECOLS(ExampleData1,3,5,8,6,4)
' Grab a Unique List of SKUS (Column 3) from a Named Range
=UNIQUE(CHOOSECOLS(ExampleData1,3))
' Grab Columns from a Named Range using a Horizontal Array Constant
=UNIQUE(CHOOSECOLS(ExampleData1, {2,3,4}))
' Grab Columns from a Named Range using a Vertical Array Constant
=UNIQUE(CHOOSECOLS(ExampleData1, {2;3;4}))
' Grab every other Odd Column from a Named Range
' - press F9 in the Formula Box to see =CHOOSECOLS(ExampleData1, {1;3;5;7;9;11;13;15;17})
=CHOOSECOLS(ExampleData1, SEQUENCE(ROUNDUP(COLUMNS(ExampleData1)/2, 0), 1, 1, 2))
' Grab every other Even Column from a Named Range
' - press F9 in the Formula Box to see =CHOOSECOLS(ExampleData1, {2;4;6;8;10;12;14;16;18})
=CHOOSECOLS(ExampleData1, SEQUENCE(ROUNDDOWN(COLUMNS(ExampleData1)/2, 0), 1, 2, 2))
' Flip a Named Range Array (reverse the order of columns in an array from left to right)
' - press F9 in the Formula Box to see =CHOOSECOLS(ExampleData1, {-1;-2;-3;-4;-5;-6;-7;-8;-9;-10;-11;-12;-13;-14;-15;-16;-17;-18})
=CHOOSECOLS(ExampleData1, SEQUENCE(COLUMNS(ExampleData1)) *-1)
' With a Cell in "I38" containing the String "1, 2, 4" Grab the specific Columns from a Dynamic Named Range Array
=CHOOSECOLS(ExampleData1, TEXTSPLIT(I38, ", ") *1)
' Extract Unique Items in a Column (or Columns) from Multiple Named Ranges using VSTACK and CHOOSECOLS
' - merge all the ranges into one with the help of the VSTACK function, and then handle the merged range with CHOOSECOLS
' - if you choose multiple Columns and one of the arrays are a single Column array then #N/A is used as a Fill for missing slots
=UNIQUE(CHOOSECOLS(VSTACK(SKUList,PlantList,ExampleData1),1))
=UNIQUE(CHOOSECOLS(VSTACK(SKUList,PlantList,ExampleData1),1,3))
=UNIQUE(CHOOSECOLS(VSTACK(SKUList,PlantList,ExampleData1),1,3,4))
' Filter the Spill Array of the third Example above by "Total Shipments" (Column 1) where 'BZ14#' holds the Spill results
' - and exclude #N/A that may Spill from another Column
=FILTER(BZ14#,INDEX(BZ14#,,1)="Total Shipments")
=FILTER(BZ14#,IFERROR(INDEX(BZ14#,,2)<>0,0))
TOROW()
Array (required) - an array or range to transform into a single row
Ignore (optional) - determines whether to ignore blanks or/and errors. Can take one of these values:
0 or omitted (default) - keep all values
1 - ignore blanks
2 - ignore errors
3 - ignore blanks and errors
Scan_by_column (optional) - defines how to scan the array:
FALSE or omitted (default) - scan the array horizontally by row
TRUE - scan the array vertically by column
With any Named Range you can use the TOROW() function to Spill an Array horizontally, for example, consider the dynamic Named Range 'SKUList' in the example Workbook (values can be text, numbers, dates, times, etc.). You can also combine this with the UNIQUE() function. An alternative is to use the TRANSPOSE() function:
' Syntax
=TOROW(array, [ignore], [scan_by_column])
' Transform a Unique SKU List into Rows
=TOROW(UNIQUE(SKUList))
=TRANSPOSE(UNIQUE(SKUList))
' TestRange1 is a 2 Column by 3 Row Named Range
' exclude blanks, set the ignore argument to 1
=TOROW(TestRange1,1)
' ignore errors, set the ignore argument to 2
=TOROW(TestRange1,2)
' skip both, blanks and errors, use 3 for the ignore argument
=TOROW(TestRange1,3)
' TestRange2 is a 2 Column by 3 Row Named Range
' Extract Unique values from a Named Range into a row
=UNIQUE(TOROW(TestRange2),TRUE)
' Extract Unique, Sorted values from a Named Range into a row
=SORT(UNIQUE(TOROW(TestRange2),TRUE), , ,TRUE )
' Read Named Range Horizontally (default by Row, Across then Down) or Vertically (by Column, Down then Across)
=TOROW(TestRange2)
=TOROW(TestRange2, ,TRUE)
' Stack Named Ranges Horizontally and convert by Row
=TOROW(HSTACK(TestRange1, TestRange2))
' Stack Named Ranges Horizontally and convert by Column
=TOROW(HSTACK(TestRange1, TestRange2), ,TRUE)
' Stack Named Ranges Vertically and convert by Row
=TOROW(VSTACK(TestRange1, TestRange2))
' and ignoring Blanks and Errors
=TOROW(VSTACK(TestRange1, TestRange2),3)
' Stack Named Ranges Vertically and convert by Column
=TOROW(VSTACK(TestRange1, TestRange2), ,TRUE)
' and ignoring Blanks and Errors
=TOROW(VSTACK(TestRange1, TestRange2),3,TRUE)
SEQUENCE()
rows - Number of rows to return
columns - [optional] Number of columns to return
start - [optional] Starting value (defaults to 1)
step - [optional] Increment between each value (defaults to 1)
With any Named Range you can use the SEQUENCE() function to Spill the Array and generate a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional, determined by rows and columns arguments. The SEQUENCE() function can also be used to generate a numeric array inside another formula which can be extremely useful. WHere SEQUENCE() stands out for me is its ability to manipulate and / or spawn Dates:
' Syntax
=SEQUENCE(rows, [columns], [start], [step])
' Generate a 5 X 5 Matrix in Steps of 2 ie. Odd Numbers 1-49
=SEQUENCE(5,5,1,2)
' Given the Year in a Cell ie. "$H$16" = '2023', Generate all of January Day Dates for the Year
=DATE($H$16,1,SEQUENCE(DAY(EOMONTH(DATE($H$16,1,1),0)),1,1))
' Repeat the above but for February to Generate 28 (and 29 on a leap year)
' the number 2 being substituted for a 1 (2 being February)
=DATE($H$16,2,SEQUENCE(DAY(EOMONTH(DATE($H$16,2,1),0)),1,1))
' Given the Year in a Cell ie. "$H$16" = '2023', Generate all of the Months Dates for that Year
=EDATE(DATE($H$16,1,1),SEQUENCE(12,1,0))
' Given the Year in a Cell ie. "$H$16" = '2023', Generate all of the Months Dates for that Year but using TEXT() to Format as Month
=TEXT(EDATE(DATE($H$16,1,1),SEQUENCE(12,1,0)),"mmmm")
GENERATING COMBINATIONS & LAMBDA (2 Lists)
Here we will use Office 365 Formula to create combinations from dynamic Named Ranges or Lists of data and then make a LAMBDA function that we can set up as a Named Range. This is an extremely dynamic and powerful reporting technique. You can create combinations built from two or more lists using the existing combinations in the list and TOCOL() or create ALL possible combinations from the lists using TOCOL together with TRANSPOSE(). You can choose to sort the data either as you build the LAMBDA Function or afterwards by wrapping the LAMBDA function in SORT() or UNIQUE()
We will demonstrate each part of the method to get to our final result, a LAMBDA Functon we can simply pass in Named Range lists. We start by joining two lists using the '@' character. Then we will use TEXTBEFORE() and TEXTAFTER() to split the lists back out and HSTACK() to stack our lists. Before we create the final LAMBDA function, we will write a test function and then finally we will create the LAMBDA function we can set up as a Named Range and then simply use somethng like this in Excel: =CreateCombinationsFrom2Lists(MaterialList,DepotList)
2 Lists to create the exact Combinations from both lists:
' use TOCOL() to join two lists exactly side by side
' in Cell "B16"
=TOCOL(MaterialList&"@"&DepotList)
' split the list on the left
=TEXTBEFORE(B16#,"@")
' split the list on the right
=TEXTAFTER(B16#,"@")
' stack the lists
=HSTACK(TEXTBEFORE(B16#,"@"),TEXTAFTER(B16#,"@"))
' the LAMBDA test function - we use this to test if the function is working correctly
=LAMBDA(array1,array2,LET(txt,TOCOL(array1&"@"&array2),HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(txt,"@"))))(MaterialList,DepotList)
' the final Named Range LAMBDA function (CreateCombinationsFrom2Lists)
=LAMBDA(array1,array2, LET(txt,TOCOL(array1&"@"&array2), HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(txt,"@"))))
' and the function we can now use in our Excel Formula by passing in any two lists (Named Ranges) or static Ranges like "A1:A1","B1:B5"
=CreateCombinationsFrom2Lists(MaterialList,DepotList)
2 Lists to create ALL of possible Combinations from both lists:
' use TOCOL() to join two lists and with the second list use TRANSPOSE() to force ALL combinations
' notice we will use SORT() and UNIQUE() to maintain how we want the combinations to look
' this means that for each Product in the Material list we will also create each Plant in the Depot list
' both will be Sorted Ascending and we will only use the Unique items within each list
' in Cell "AM16"
=TOCOL(SORT(UNIQUE(MaterialList))&"@"&TRANSPOSE(SORT(UNIQUE(DepotList))))
' split the list on the left
=TEXTBEFORE(AM16#,"@")
' split the list on the right
=TEXTAFTER(AM16#,"@")
' stack the lists
=HSTACK(TEXTBEFORE(AM16#,"@"),TEXTAFTER(AM16#,"@"))
' the LAMBDA test function - we use this to test if the function is working correctly
' we use SORT() and UNIQUE() to maintain the order we want ie. Product then Plant Ascending
' with SORT()
=LAMBDA(array1,array2,LET(txt,TOCOL(SORT(UNIQUE(array1))&"@"&TRANSPOSE(SORT(UNIQUE(array2)))),HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(txt,"@"))))(MaterialList,DepotList)
' without SORT()
=LAMBDA(array1,array2,LET(txt,TOCOL(UNIQUE(array1)&"@"&TRANSPOSE(UNIQUE(array2))),HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(txt,"@"))))(MaterialList,DepotList)
' the final Named Range LAMBDA function (CreateAllCombinationsFrom2Lists)
=LAMBDA(array1,array2, LET(txt,TOCOL(SORT(UNIQUE(array1))&"@"&TRANSPOSE(SORT(UNIQUE(array2)))), HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(txt,"@"))))
' and the function we can now use in our Excel Formula by passing in any two lists (Named Ranges) or static Ranges like "A1:A1","B1:B5"
=CreateAllCombinationsFrom2Lists(MaterialList,DepotList)
GENERATING COMBINATIONS & LAMBDA (3 Lists)
Here we will use the first example above but add in another list so that we have the combinations for both a 'DEL' (Delivered) Key Figure and a 'FCST' (Forecast) Key Figure. To explain the process we will again use steps to join, split out and stack the lists, then build a LAMBDA test function and finally create a Named Range using the LAMBDA function with optional Sort capability
' begin by combining all of the lists using TOCOL() and TRANSPOSE() - notice the use of both '@' and '#' as separators
=TOCOL(MaterialList&"@"&DepotList&"#"&TRANSPOSE(KeyFigures))
' split out the lists - using the results of the above TOCOL() Cell "B19"
=TEXTBEFORE(B19#,"@")
=TEXTBEFORE(TEXTAFTER(B19#,"@"),"#")
=TEXTAFTER(B19#,"#")
' the LAMBDA test function - we use this to test if the function is working correctly
=LAMBDA(array1,array2,array3,LET(txt,TOCOL(array1&"@"&array2&"#"&TRANSPOSE(array3)),HSTACK(TEXTBEFORE(txt,"@"),TEXTBEFORE(TEXTAFTER(txt,"@"),"#"),TEXTAFTER(txt,"#"))))(MaterialList,DepotList,KeyFigures)
' the final Named Range LAMBDA function (CreateAllCombinationsFrom3Lists) that we create as a Named Range
=LAMBDA(array1,array2,array3,LET(txt,TOCOL(array1&"@"&array2&"#"&TRANSPOSE(array3)),HSTACK(TEXTBEFORE(txt,"@"),TEXTBEFORE(TEXTAFTER(txt,"@"),"#"),TEXTAFTER(txt,"#"))))
' and the function we can now use in our Excel Formula by passing in any three lists (Named Ranges) or static Ranges like "A1:A1","B1:B5","C1:C2"
=CreateCombinationsFrom3Lists(MaterialList,DepotList,KeyFigures)
' sorting the results of the LAMBDA - you can either use a Pre-sort on the LAMBDA or a Post-sort on the Named Range LAMBDA function
' Pre-sort by the Material as default Ascending - use a Sort() on the HSTACK() array and add a LAMBDA called CreateSortedCombinationsFrom3Lists as a Named Range with the below formula
=LAMBDA(array1,array2,array3,LET(txt,TOCOL(array1&"@"&array2&"#"&TRANSPOSE(array3)),SORT(HSTACK(TEXTBEFORE(txt,"@"),TEXTBEFORE(TEXTAFTER(txt,"@"),"#"),TEXTAFTER(txt,"#")))))
=CreateSortedCombinationsFrom3Lists(MaterialList,DepotList,KeyFigures)
' Post-sort by the Material as default Ascending
=SORT(CreateCombinationsFrom3Lists(MaterialList,DepotList,KeyFigures))
' Pre-sort by the Material as default Ascending - use a Sort() on the HSTACK() array & change the Key Figure Order, "DEL" then "FCST"
=LAMBDA(array1,array2,array3,LET(txt,TOCOL(array1&"@"&array2&"#"&TRANSPOSE(SORT(array3,1,1))),SORT(HSTACK(TEXTBEFORE(txt,"@"),TEXTBEFORE(TEXTAFTER(txt,"@"),"#"),TEXTAFTER(txt,"#")))))
=CreatedSortedCombinationsFrom3ListsSwapKeyFigureOrder(MaterialList,DepotList,KeyFigures)
GENERATING COMBINATIONS & LAMBDA (4 Lists)
We can now expand further and add another list. Consider if we had both HL and PC units of measure and we wanted to create a list of Material, Depot, Key Figures and Units of Measure so that we could lookup or aggregate weekly volumes, we could build a 4 Input LAMBDA function to do this easily. We can then Post-sort our list by Material, Depot, Key Figure and have all of the 'HL' combinations output first and then all of the 'PC' combinations output last - this can be very powerful when aggregating data in order to further Pivot out or to build Overview Sheets and Tables from. I will demonstrate various methods of both Joining and Sorting as we build the LAMBDA function
' begin by combining all of the lists using TOCOL() and TRANSPOSE() - notice the use of both '@' and '#' as separators
' METHOD A
=TOCOL(TOCOL(MaterialList&"@"&DepotList&"#"&TRANSPOSE(KeyFigures))&"$"&TRANSPOSE(UnitsOfMeasure))
' you can sort at any stage - just check that the results are what you expect
' here I sort by Material and Depot Lists Ascending AND by Key Figure Ascending AND by Units of Measure Descending
=TOCOL(TOCOL(SORT(MaterialList&"@"&DepotList)&"#"&TRANSPOSE(SORT(KeyFigures,1,1)))&"$"&TRANSPOSE(SORT(UnitsOfMeasure,1,-1)))
' begin by combining all of the lists using TOCOL() and TRANSPOSE() - notice the use of both '@' and '#' as separators
' METHOD B
=TOCOL(TOCOL(TOCOL(MaterialList&"@"&DepotList)&"#"&TRANSPOSE(KeyFigures))&"$"&TRANSPOSE(UnitsOfMeasure))
' here I sort by Material and Depot Lists Ascending AND by Key Figure Ascending AND by Units of Measure Descending
=TOCOL(TOCOL(TOCOL(SORT(MaterialList&"@"&DepotList))&"#"&TRANSPOSE(SORT(KeyFigures,1,1)))&"$"&TRANSPOSE(SORT(UnitsOfMeasure,1,-1)))
' split out the lists - using the results of the above TOCOL() Cell "AY21"
=TEXTBEFORE(AY21#,"@")
=TEXTBEFORE(TEXTAFTER(AY21#,"@"),"#")
=TEXTBEFORE(TEXTAFTER(AY21#,"#"),"$")
=TEXTAFTER(AY21#,"$")
' create the LAMBDA function using Method B
=LAMBDA(array1,array2,array3,array4,LET(txt,TOCOL(TOCOL(TOCOL(array1&"@"&array2)&"#"&TRANSPOSE(array3))&"$"&TRANSPOSE(array4)),HSTACK(TEXTBEFORE(txt,"@"),TEXTBEFORE(TEXTAFTER(txt,"@"),"#"),TEXTBEFORE(TEXTAFTER(txt,"#"),"$"),TEXTAFTER(txt,"$"))))(MaterialList,DepotList,KeyFigures,UnitsOfMeasure)
' Post-sort by Units of Measure
=SORT(CreateCombinationsFrom4Lists(MaterialList,DepotList,KeyFigures,UnitsOfMeasure),4)
' Post-sort by Material and then by Units of Measure
=SORT(SORT(CreateCombinationsFrom4Lists(MaterialList,DepotList,KeyFigures,UnitsOfMeasure)),4)
TRANSPOSE CROSS TABLE TO LIST
There are quite a few ways to transpose data stored in a Cross Table (Columns and Rows) back to a single list, sorted or otherwise. You can use a Pivot Table, VBA or use the Office365 Formula. Here I will demonstrate a quick method using Formula both with Helper Columns and Formula and with just Formula. We will Transpose data stored as Materials (Rows) and Plants (Columns) with Qty. (values) in any order into a single sorted list of Material by Plant by Qty. To see how I do this download the Worksheet below
This is what we have:
This is what we want:
Download Example File with Office365 Formula: Transpose data stored in a Cross Table.xlsx
Download a VBA Example: TransposeCrossTableDataVBA.xlsm
' transpose data stored in a Cross Table using Formula and Helper Columns
' sorted by SKU and Depot
' Formula in Cells "S6:X6"
=SORT(TOCOL(TOCOL(SKUS)&"@"&Depots))
=TEXTBEFORE(S6#,"@")
=TEXTAFTER(S6#,"@")
=MATCH(U6,Depots,0)
=MATCH(INT(T6),SKUS,0)
=INDEX(Data,W6,V6)
' more advanced - transpose data stored in a Cross Table using Formula
' sorted by SKU and Depot
' Formula stored in Cells "Z6:AC6"
=SORT(TOCOL(TOCOL(SKUS)&"@"&Depots))
=TEXTBEFORE(Z6#,"@")
=TEXTAFTER(Z6#,"@")
=INDEX(Data,MATCH(INT(AA6),SKUS,0),MATCH(AB6,Depots,0))
' more concise - transpose data stored in a Cross Table using compact Formula
' sorted by SKU and Depot
' Formula stored in Cells "AE6:AG6"
=TEXTBEFORE(SORT(TOCOL(TOCOL(SKUS)&"@"&Depots)),"@")
=TEXTAFTER(SORT(TOCOL(TOCOL(SKUS)&"@"&Depots)),"@")
=INDEX(Data,MATCH(INT(AE6),SKUS,0),MATCH(AF6,Depots,0))
TRANSPOSE CROSS TABLE TO LIST & LAMBDA
Here we will create a LAMBDA function to transpose data stored in a Cross Table to a sorted list, test it and then add it as a Named Range before finally using it to transpose the data as per the example above. I also add a method to Filter out Zero values ("0"). To see how I do all of the methods using Office365 Formula and the LAMBDA function, download the Worksheets below
Download Example File with Office365 Formula: Transpose data stored in a Cross Table (LAMBDA).xlsx
Download Example File with Office365 Formula: Transpose data stored in a Cross Table (LAMBDA) with FILTER.xlsx
' build the LAMBDA function
' initial Sort() and concat in Cell "AI6"
=SORT(TOCOL(SKUS&"@"&Depots)&"#"&TOCOL(Data))
=TEXTBEFORE(AI6#,"@")
=TEXTAFTER(TEXTBEFORE(AI6#,"#"),"@")
=TEXTAFTER(AI6#,"#")
' create the LAMBDA test function
=LAMBDA(array1,array2,array3,
LET(txt,SORT(TOCOL(array1&"@"&array2)&"#"&TOCOL(array3)),
HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(TEXTBEFORE(txt,"#"),"@"),TEXTAFTER(txt,"#"))))(SKUS,Depots,Data)
' add a Named Range called 'CrossTableToList' with the following
=LAMBDA(array1,array2,array3,
LET(txt,SORT(TOCOL(array1&"@"&array2)&"#"&TOCOL(array3)),
HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(TEXTBEFORE(txt,"#"),"@"),TEXTAFTER(txt,"#"))))
' use the LAMBDA function
=CrossTableToList(SKUS,Depots,Data)
' to Filter out zero values ("0") or Blanks use FILTER() on HSTACK()
' create the LAMBDA test function
=LAMBDA(array1,array2,array3,LET(txt,SORT(TOCOL(array1&"@"&array2)&"#"&TOCOL(array3)),FILTER(HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(TEXTBEFORE(txt,"#"),"@"),TEXTAFTER(txt,"#")),TEXTAFTER(txt,"#")>"0")))(SKUS,Depots,Data)
' add a Named Range called 'CrossTableToListExcludeZeros' with the following
=LAMBDA(array1,array2,array3, LET(txt,SORT(TOCOL(array1&"@"&array2)&"#"&TOCOL(array3)), FILTER(HSTACK(TEXTBEFORE(txt,"@"),TEXTAFTER(TEXTBEFORE(txt,"#"),"@"),TEXTAFTER(txt,"#")),TEXTAFTER(txt,"#")>"0")))
' use the LAMBDA function
=CrossTableToListExcludeZeros(SKUS,Depots,Data)