The formula requires only a minor edit in the last step, since the information sought is already created in the correct form by the existing formula, but not displayed. To display it, we use the HSTACK function in the last step. As I reviewed each step to offer some documentation, I noted one thing that should be addressed: I hardwired a "4" in the "header" formula, and that is now changed to
header,DROP(CHOOSEROWS(ary,2),,DaysBeginAryCol-1), where DaysBeginAryCol-1 replaces the 4. This step drops the first several columns, leaving only the "days" columns. Here is a very brief sample to show the revised formual (note that I've inserted some line breaks to make each step more obvious:
| MrExcel_20231024 (version 1) (version 1).xlsx |
|---|
|
|---|
| A | B | C | D |
|---|
| 1 | Scheduled Date | HUB Name | Child Node | Missed Amps |
|---|
| 2 | 10/23 | HUB Location | VAALD09D0A | 1 |
|---|
| 3 | 10/23 | HUB Location | VAALD0210A | 1 |
|---|
| 4 | 10/23 | HUB Location | VAALD0100D | 1 |
|---|
|
|---|
Documentation isn't supported within these formulas, and as they grow in complexity, that becomes a problem. Here is an expanded version of the formula with comments about each step:
Comments added on LET formula used for Dclark015
LET(
ary,'TEST Template'!A1:O21, // assign data range to variable ary
DaysBeginAryCol,5, // assign the column number of ary where the "days" data begin to variable DaysBeginAryCol
data,FILTER(IF(ISNUMBER(ary),ary,ary&""),INDEX(ary,,1)<>""), // condition the ary array by forcing blanks (which FILTER was considering to be 0's) to be blanks, and then remove rows where the 1st column (Nodes) is empty...and assign this new trimmed down array to variable data
numcols,COLUMNS(data), // assign number of columns in data array to numcols
NumDayCols,numcols-DaysBeginAryCol+1, // determine number of columns that contain "days" information and assign that value to variable NumDayCols
dleft,TAKE(data,,1), // assign 1st column of "data" array (the consolidated version of the original source "ary") to variable dleft (the left side of the "data" array
dright,TAKE(data,,-NumDayCols), // from the right side of the "data" array, take only the columns containing "days" information and assign those columns (which form a multi-column array) to variable dright (essentially the right side of the "data" array
data2,HSTACK(dleft,dright), // combine dleft and dright to form a new data array called data2 (this is identical to the "data" array with a few columns ommitted)
locrows,LET(a,SEQUENCE(ROWS(data2))*(TAKE(data2,,1)="Node"),FILTER(a,a<>0)-1), // create a vertical array called "a" that consists of either 0's or the array row position (of array data2) where the word "Node" appears in column1 of data2...and then feed this array "a" into a FILTER function to remove the 0's, returning a smaller array called "locrows" that shows the row locations in data2 where the Hub Location should be found (assumes the Hub Location always appears in the row before the word "Node" in the original source table ary)
locary,MAKEARRAY(ROWS(data2),1,LAMBDA(r,c,IF(ISNUMBER(MATCH(r,locrows,0)),INDEX(data2,r,c),""))), // a little tricky here...this creates a new array called "locary" that is generated by feeding a sequence of array row numbers for the data2 array into a LAMBDA function. The array is to be 1 column wide. For each array row number, we determine if the array row number can be found anywhere within the small "locrows" array, and if that is TRUE, then we assign the corresponding value from the "data2" array to this position in the "locary" array, and if the evaluation results in FALSE, then a blank is assigned to the "locary" array position. Essentially this step creates a single column array that contains either blanks or the name of the Hub Location in the same position where it appears in array "data2".
idx,SCAN("",locary,LAMBDA(acc,val,IF(val="",acc,+val))), // the "locary" array is fed into a SCAN function to create a new array called "idx". SCAN performs an element by element operation via a LAMBDA function. Here the LAMBDA begins with a blank assigned to its accumulator variable, and if a "locary" array element is blank, then the accumulator value is assigned to that position in the "idx" array, otherwise the value of the "locary" element is assigned to the "idx" array position. This step acts like a "Fill Down" operation so that the "idx" array will look like the "locary" array except where blanks appear in the "locary" array will now have the Hub location.
fintbl,HSTACK(idx,data2), // the data2 array is horizontally appended to the "idx" array (a single column array containing Hub Locations), essentially creating a new first column showing the Hub Location for each row...although this array still has some unneeded rows that need to be removed
fintbl2,FILTER(fintbl,NOT(ISNUMBER(MATCH(SEQUENCE(ROWS(fintbl)),locrows,0)))), // this removes the rows from the "fintbl" array that correspond to the original Hub Location rows (the ones shown in "locrows", which are no longer needed since that information has been transformed into the first column of the "fintbl" array)
fintbl3,FILTER(fintbl2,CHOOSECOLS(fintbl2,2)<>"Node"), // this removes the rows from the "fintbl2" array where the word "Node" appears in the 2nd column of "fintbl2"...again these rows are no longer needed, as they were only used intiallly to determine where we should find the Hub Locations
data3,TAKE(fintbl3,,-NumDayCols), // similar to how the "dright" array was formed above, this takes all of the right side columns containing "days" information from the transformed and consolidated "fintbl3" array
data3scol,TOCOL(data3,,TRUE), // converts the multiple columns of the "data3" array (all of the "days" information) and stacks them into a single column, preserving blank entries
locnode,TAKE(fintbl3,,2), // similar to how the "dleft" array was formed above, this takes the two leftmost columns (containing Hub Locations and Nodes) of the "fintbl3" array...so this step and "data3" just above split "fintbl3" into two separate arrays. Just above, "data3" was transformed into a single column stacked array, so it now becomes necessary to stack this "locnode" on top of itself multiple times to correspond to information in "data3scol"
expln,CHOOSEROWS(locnode,MOD(SEQUENCE(ROWS(locnode)*NumDayCols,,0),ROWS(locnode))+1), // as alluded to in the previous step, this step will expand the Hub Location-Node array ("locnode") multiple times. Starting inside this formula, SEQUENCE creates a vertical array whose length is equal to the length of "locnode" multiplied by NumDayCols, and the sequence begins at 0. If we then MOD this sequence with number of rows in "locnode", we'll get a long array consisting of repeating sequences {0;1;2;...;ROWS(locnode)-1}. And if we then add 1 to this result, we'll generate a long array of repeating sequences {1;2;3;...ROWS(locnode)}, which are the array row numbers of "locnode" that correspond to the information in "data3scol". CHOOSEROWS is used to perform this row extraction from "locnode", to form the "expln" array whose rows correspond to the rows in "data3scol".
header,DROP(CHOOSEROWS(ary,2),,DaysBeginAryCol-1), // returns to the original "ary" data array and extracts the 2nd row, and then drops the columns to the left of the 1st column where "days" information begins, leaving a horizontal array of dates (assumes the dates appear in row 2 of "ary")
datecol,TOCOL(IFNA(EXPAND(header,ROWS(data3)),header),,TRUE), // just like a scheme was needed for repeating the Hub Location-Node rows, a similar scheme is needed to repeat the date headers so that they will correspond to the single column data3scol array (the "days" information). EXPAND creates an array with the date "header" as the first row and then adds more rows for a total number of ROWS(data3) rows, which means the array length matches the length of the data3 array. By default, #N/A errors are introduced when the expanded array length exceeds that of the array to expand. This behavior is exploited by trapping it with an IFNA function to replace the error with the corresponding element from the date "header" array. The result is a date "header" array repeated for each row in "data3". We then convert this array of data headers into a single stacked column, taking each column from left to right and stacking it below the one to its left (same effective stacking order used when "expln" was formed)
final,HSTACK(datecol,expln), // combines the date single column array "datecol" with "expln" to create a three column array of Date, Hub Location, and Node that correspond to every row in "date3scol"
finfltr,FILTER(final,N(data3scol)<>0), // the original final FILTER operates on the "final" three-column array of Date, Hub Location, and Node and retains only those where "data3scol" is not 0. But recall that "data3scol" is the information found within the "days" columns. So here it is used only to determine whether a row of information (Date/Hub/Node) should be displayed.
If you want to see the "days" information, then this last step of the formula is modified. So before we feed the "final" array into FILTER, we'll first HSTACK "final" with "data3scol" and then perform the FILTER on the 4-column array...so the new final step will be...
finfltr,FILTER(HSTACK(final,data3scol),N(data3scol)<>0),
finfltr)
