Working with a formula that pulls data from multiple columns

Dclark015

New Member
Joined
Mar 6, 2023
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon,

I am working on a spreadsheet and could use some help. I have the formulas I need down however there is a lot of data I need to pull and was hoping there is a way to make it easier. The formula I am currently using is =IF('TEST Template'!A6<>"",'TEST Template'!$A$2,"") It does pull from 2 sheets. I am trying to build it so that I can just copy it down the column by hitting the green dot. This works however it's taking way more time than I thought it would to capture everything I need because of my needs.

If possible I need the A6 row to only carry to 360 and then start over as B6 and so forth until I hit AH. For the second item I need to keep the $A$2 locked however I also need the A to change a B when the first A changes to a B and keep that through the 360. If I can just get the first letters as stated I can just go in every 360 and change the second letter as needed.

Any help is great and thanks.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Good Afternoon KRice!! I've been trying to reverse-engineer your formula so I could add another column in the results. I was asked to add a column with the number of amps that are in the range denoting the day the work is on. Where I had the 1, 2, or 3's. Is that something that is easily added (I'd love the explanation if available) to the collected data?

Thanks again,
 
Upvote 0
I was asked to add a column with the number of amps that are in the range denoting the day the work is on. Where I had the 1, 2, or 3's.
Tell me more, please. Let's take an example...suppose we see this in the Test Template:
MrExcel_20231024 (version 1) (version 1).xlsx
ABCDE
1OP TicketDayMonday
2Date10/23
3Max Quota
4HUB LocationPassives
5Node
6VAALD09D0Ay1
7VAALD0210Ay1
8VAALD0100Dy1
9VAALD02M0Cy2
10VAALD0520Cy3
TEST Template

Are you saying you'd like the output to look like this in the Test Daily Template table?
MrExcel_20231024 (version 1) (version 1).xlsx
ABCD
1Scheduled DateHUB NameChild NodeCurrent (A)
210/23HUB LocationVAALD09D0A1
310/23HUB LocationVAALD0210A1
410/23HUB LocationVAALD0100D1
510/23HUB LocationVAALD02M0C2
610/23HUB LocationVAALD0520C3
TEST Daily Template
Cell Formulas
RangeFormula
A2:C26A2=LET(ary,'TEST Template'!A1:O21,DaysBeginAryCol,5, data,FILTER(IF(ISNUMBER(ary),ary,ary&""),INDEX(ary,,1)<>""), numcols,COLUMNS(data),NumDayCols,numcols-DaysBeginAryCol+1, dleft,TAKE(data,,1),dright,TAKE(data,,-NumDayCols),data2,HSTACK(dleft,dright), locrows,LET(a,SEQUENCE(ROWS(data2))*(TAKE(data2,,1)="Node"),FILTER(a,a<>0)-1), locary,MAKEARRAY(ROWS(data2),1,LAMBDA(r,c,IF(ISNUMBER(MATCH(r,locrows,0)),INDEX(data2,r,c),""))), idx,SCAN("",locary,LAMBDA(acc,val,IF(val="",acc,+val))), fintbl,HSTACK(idx,data2), fintbl2,FILTER(fintbl,NOT(ISNUMBER(MATCH(SEQUENCE(ROWS(fintbl)),locrows,0)))), fintbl3,FILTER(fintbl2,CHOOSECOLS(fintbl2,2)<>"Node"), data3,TAKE(fintbl3,,-NumDayCols),data3scol,TOCOL(data3,,TRUE), locnode,TAKE(fintbl3,,2),expln,CHOOSEROWS(locnode,MOD(SEQUENCE(ROWS(locnode)*NumDayCols,,0),ROWS(locnode))+1), header,DROP(CHOOSEROWS(ary,2),,4),datecol,TOCOL(IFNA(EXPAND(header,ROWS(data3)),header),,TRUE),final,HSTACK(datecol,expln),FILTER(final,N(data3scol)<>0))
Dynamic array formulas.
 
Upvote 0
Correct I am looking to make a 4th column that would look like the one below.


Scheduled DateHubChild NodeMissed (insert Number) Amps
11/7/2023Baltimore CityMDDSD04V0B
11/7/2023Baltimore CityMDMED02L0A
11/7/2023CarrollMDWED02S0A
11/7/2023Frederick/MartinsburgMDGPD03N0C
11/7/2023Lynchburg/BlacksburgVASAD0030C (Salem)
11/7/2023MillersvilleMDGAD03C0A
11/7/2023MontgomeryMDHCD05A0C
11/7/2023MontgomeryMDHCD02J0A
11/7/2023MontgomeryMDGBD01T0A
 
Upvote 0
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
ABCD
1Scheduled DateHUB NameChild NodeMissed Amps
210/23HUB LocationVAALD09D0A1
310/23HUB LocationVAALD0210A1
410/23HUB LocationVAALD0100D1
TEST Daily Template
Cell Formulas
RangeFormula
A2:D26A2=LET(ary,'TEST Template'!A1:O21,DaysBeginAryCol,5, data,FILTER(IF(ISNUMBER(ary),ary,ary&""),INDEX(ary,,1)<>""), numcols,COLUMNS(data), NumDayCols,numcols-DaysBeginAryCol+1, dleft,TAKE(data,,1), dright,TAKE(data,,-NumDayCols), data2,HSTACK(dleft,dright), locrows,LET(a,SEQUENCE(ROWS(data2))*(TAKE(data2,,1)="Node"),FILTER(a,a<>0)-1), locary,MAKEARRAY(ROWS(data2),1,LAMBDA(r,c,IF(ISNUMBER(MATCH(r,locrows,0)),INDEX(data2,r,c),""))), idx,SCAN("",locary,LAMBDA(acc,val,IF(val="",acc,+val))), fintbl,HSTACK(idx,data2), fintbl2,FILTER(fintbl,NOT(ISNUMBER(MATCH(SEQUENCE(ROWS(fintbl)),locrows,0)))), fintbl3,FILTER(fintbl2,CHOOSECOLS(fintbl2,2)<>"Node"), data3,TAKE(fintbl3,,-NumDayCols), data3scol,TOCOL(data3,,TRUE), locnode,TAKE(fintbl3,,2), expln,CHOOSEROWS(locnode,MOD(SEQUENCE(ROWS(locnode)*NumDayCols,,0),ROWS(locnode))+1), header,DROP(CHOOSEROWS(ary,2),,DaysBeginAryCol-1), datecol,TOCOL(IFNA(EXPAND(header,ROWS(data3)),header),,TRUE), final,HSTACK(datecol,expln), FILTER(HSTACK(final,data3scol),N(data3scol)<>0))
Dynamic array formulas.


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)
 
Upvote 1
Solution
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)
You sir are a Gentlemen, a scolar, and a savant. Thank you so much for the help and specifically the breakdown that I will be studying to try and build these myself. I knew what I wanted the program to do however I just couldn't figure it out.

Thank you again,
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top