Working with a formula that pulls data from multiple columns

[Dclark015]

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.

 

[Dclark015]

I guess I stumped the world

 

[KRice]

Starting with some notional data in the 'TEST Template' sheet, such as this:

MrExcel_20231024.xlsx
	A	B	C	D
1
2	FV in A2	FV in B2	FV in C2	FV in D2
3
4
5
6	a6	b6	c6
7	a7		c7	d7
8		b8		d8
9	a9	b9
10		b10	c10
11	a11		c11	d11
12		b12	c12
TEST Template

The following creates a new array based on the conditional you've described, replacing any values with the fixed values in the corresponding columns while retaining blanks. It's not clear to me what you want to do with the result. I'm assuming you want to stack each of the array columns to create a single long column, but let me know if that is not correct.

MrExcel_20231024.xlsx
	A
1	Results
2	FV in A2
3	FV in A2
4
5	FV in A2
6
7	FV in A2
8
9	FV in B2
10
11	FV in B2
12	FV in B2
13	FV in B2
14
15	FV in B2
16	FV in C2
17	FV in C2
18
19
20	FV in C2
21	FV in C2
22	FV in C2
23
24	FV in D2
25	FV in D2
26
27
28	FV in D2
29
Dclark015

Cell Formulas
Range	Formula
A2:A29	A2	=LET(ary,'TEST Template'!A6:D12,fv,'TEST Template'!A2:D2,cnum,COLUMNS(ary),rnum,ROWS(ary),aryfv,MAKEARRAY(rnum,cnum,LAMBDA(r,c,INDEX(fv,1,c))),arymap,MAP(ary,aryfv,LAMBDA(a,b,IF(a<>"",b,""))),TOCOL(arymap,,TRUE))
Dynamic array formulas.

 

[Dclark015]

Thanks for looking into this. I'll add some pictures and more information as best I can. I'm not able to install XL2BB because of work PC configurations. I get an error saying this can't be used in protected mode and the store is locked out as well.

The information on this sheet (Named TEST Template) is where I am pulling the data from and then adding it to the second sheet (named TEST Daily Template) The data I am trying to pull is if E6(Sixth Cell under Monday that has a 1) has a number in it then it will pull the date from E2(First Date under Monday). This will return the date on A2 on the sheet "Test Daily Template".

The issue that I am having is that the Hub locations and cells below them go to row 400. Having to copy it down and then going to each line after about 20 is becoming a very long process. The picture below only shows a 5-day set of columns however the dates will actually cover the fiscal month 22-21st minus weekends. Thus causing me to create code/formulas for all of those cells as well to go onto that second page. Once the formula is completed I would like to also add something to hide a line if it was blank so that any other cell filled would flow to the top.

Yesterday while working on this I am also working on other Ideas on how to build (maybe a pivot) to get my information easier and more automated. My issue is the person who created the First sheet is on vacation and I won't be able to see how he got the data to it and from where or if it was all just cut and pasted from other files.

This is a lot of figuring out. If it just sounds too complicated I'm good with that answer also and I will work on getting it from another location or use a pivot that one of my managers had suggested.


SHEET 1(TEST Template)

	OP Ticket		Day	Monday	Tuesday	Wednesday	Thursday	Friday
		Date	10/23	10/24	10/25	10/26	10/27
		Max Quota
HUB Location	Passives
Node
VAALD09D0A	y		1	1		1
VAALD0210A	y		1	1		1
VAALD0100D	y		1			1
VAALD02M0C	y		1					1
VAALD0520C	y		3				3
	OP Ticket		Day	Monday	Tuesday	Wednesday	Thursday	Friday
		Date	10/23	10/24	10/25	10/26	10/27
		Max Quota
Different Hub	Passives
Node
MDFKD0570D			1					1
MDMED02S0C			1
MDDSD06V0A			1
MDDSD02R0C	Y		2	2
MDFKD03J0A	Y		2	2

SHEET 2 (TEST Daily Template) Where data is being collected.

Scheduled Date	Business Partner Name	Primary Node Reference	Hub	City	Child Node	Tier Classification	Task Type
10/23/2023	GEX	HUB Location			VAALD09D0A	2A Proactive Digital
10/23/2023	GEX	Node			VAALD0210A	2A Proactive Digital

 

[KRice]

Thanks for providing some clarification. What distinguishes the upper set of source data from the lower one? I see "HUB Location" and "Node" to the upper left of the 1st group, and "Different Hub" and "Node" to the upper left of the 2nd group. Is any of that information to be carried through and applied to the entire set below it? I ask because in the Results table, there is a column called "Primary Node Reference" that appears to use the 1st label (HUB Location) for the 1st item in the 1st set of data, but then the 2nd label (Node) is used for the 2nd item in the 1st set of data. I'm trying to understand where the other information in the Results table comes from (GEX, node information, 2A Proactive Digital, etc.).

The approach I described above could be easily adjusted to remap one table with the fixed value substitutions, but your results table seems to be pulling other information into it...and the approach I described doesn't do that. Depending on your answer to the question above, it might be easier to port the data over to Power Query and transform it there to create a final results table that would then be written back onto an Excel worksheet...a VBA solution would work too. But please post back to explain where the other information comes from.

 

[Dclark015]

You're Welcome and thanks for continuing to look at this...

The source data in Hub location and Different Hub are geological areas. Such as in California or Colorado, the "node" name will go into the "Child Node" location on the second sheet. When a "node" is listed as needing work done in it then a number 1 will be placed under the date the work will be completed and the formula pulls that date and adds it under "Scheduled Date".

I'm trying to understand where the other information in the Results table comes from (GEX, node information, 2A Proactive Digital, etc.).

For this sheet, all work is done by GEX. I have a formula built so when the date shows under "Scheduled Date" then it puts in GEX under "Business Partner Name". I also have the same type of formula for the "Tier Classification" column.
For the cells under "HUB" I have that built to pull the "Hub" location. The column with "primary node reference" will be left blank.

There are a total of 25 "Hub Locations" below the two I listed as well. Each one has a total of 20 spots to use for the nodes.

 

[KRice]

Okay...I was thinking about trying to form much of the output table at once. I'm assuming:

1. the "nodes" are the text strings like VAALD09D0A, etc.
2. that a given node appears only one time in the worksheet
3. you want the results table to be a continuous one, where any nodes that have scheduled work will appear one after the other, regardless of whether those nodes are found in the 1st subtable, 2nd subtable, etc.
4. that any value greater than 0 in the subtables means that work is scheduled for that day, therefore the node and its associated HUB Name should be included in the summary
5. that the word "Node" appears above each subtable and that the HUB Name for the subtable appears immediately above the word "Node" (this is used to identify where the subtable begins so that the HUB Name can be filled down for each item (node) in the subtable).

The following assumes your data layout looks like the sample below (please check, as I noted some cell spacing issues--I think--in your post #4).
Then a single long formula transforms the input range (the "ary" variable which you'll adjust to your actual range reference) into a three column output summarizing Scheduled Date, HUB Name, and Child Node. This is probably better done in Power Query, but I was curious about using some of the newer Excel functions to essentially unpivot your source data and fill down information common to each subtable.

MrExcel_20231024.xlsx
	A	B	C	D	E	F	G	H	I
1		OP Ticket		Day	Monday	Tuesday	Wednesday	Thursday	Friday
2			Date		10/23	10/24	10/25	10/26	10/27
3			Max Quota
4	HUB Location	Passives
5	Node
6	VAALD09D0A	y			1	1	1
7	VAALD0210A	y			1	1	1
8	VAALD0100D	y			1		1
9	VAALD02M0C	y			1				1
10	VAALD0520C	y			3			3
11
12		OP Ticket		Day	Monday	Tuesday	Wednesday	Thursday	Friday
13			Date		10/23	10/24	10/25	10/26	10/27
14			Max Quota
15	Different Hub	Passives
16	Node
17	MDFKD0570D				1				1
18	MDMED02S0C				1
19	MDDSD06V0A				1
20	MDDSD02R0C	Y			2	2
21	MDFKD03J0A	Y
TEST Template

MrExcel_20231024.xlsx
	A	B	C
1	Scheduled Date	HUB Name	Child Node
2	10/23	HUB Location	VAALD09D0A
3	10/23	HUB Location	VAALD0210A
4	10/23	HUB Location	VAALD0100D
5	10/23	HUB Location	VAALD02M0C
6	10/23	HUB Location	VAALD0520C
7	10/23	Different Hub	MDFKD0570D
8	10/23	Different Hub	MDMED02S0C
9	10/23	Different Hub	MDDSD06V0A
10	10/23	Different Hub	MDDSD02R0C
11	10/24	HUB Location	VAALD09D0A
12	10/24	HUB Location	VAALD0210A
13	10/24	Different Hub	MDDSD02R0C
14	10/25	HUB Location	VAALD09D0A
15	10/25	HUB Location	VAALD0210A
16	10/25	HUB Location	VAALD0100D
17	10/26	HUB Location	VAALD0520C
18	10/27	HUB Location	VAALD02M0C
19	10/27	Different Hub	MDFKD0570D
TEST Daily Template

Cell Formulas
Range	Formula
A2:C19	A2	=LET(ary,'TEST Template'!A1:I21, data,FILTER(IF(ISNUMBER(ary),ary,ary&""),INDEX(ary,,1)<>""), data2,CHOOSECOLS(data,1,5,6,7,8,9), 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,,-5),data3scol,TOCOL(data3,,TRUE), locnode,TAKE(fintbl3,,2),expln,CHOOSEROWS(locnode,MOD(SEQUENCE(ROWS(locnode)*5,,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.

 

[Dclark015]

This worked perfectly!! Thank you. I am now able to pull all of the data which goes to I337. I made the change and it worked but if I go past I ( could go to AH but not needed) it breaks it. I'm not sure about the "ARY" you said I could change for my range reference. However, it works great for what is needed and now I can easily turn that data into a pivot or extrapolate any of the A, B, and C columns into another sheet to make it fit the format that is being used.

Thank you again your help is greatly appreciated.

 

[KRice]

You're welcome...I'm happy to help. That's great that you got it working. Good point about expanding the range. I had another look and noticed a few places where I assumed there would only be 5 day columns. I've attempted to fix that with this version and expanded the source table to column O to try it out (you should be able to go farther). This version requires you to define two things in the formula, both on the first line: 1) specify the range reference for the entire source data array, called "ary", and 2) indicate the column number in "ary" where the days begin (here its the 4th column of "ary", and I called this variable DaysBeginAryCol). You can rename any of these variables to suit your purposes and perhaps to make the formula easier to understand (just change all instances of the variable name). The two user inputs just described are then used to determine the number of "day columns" and to extract the relevant portions of "ary".

MrExcel_20231024 (version 1) (version 1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		OP Ticket		Day	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday
2			Date		10/23	10/24	10/25	10/26	10/27	10/30	10/31	11/1	11/2	11/3	11/6
3			Max Quota
4	HUB Location	Passives
5	Node
6	VAALD09D0A	y			1	1	1				1	1		1
7	VAALD0210A	y			1	1	1
8	VAALD0100D	y			1		1
9	VAALD02M0C	y			1				1
10	VAALD0520C	y			3			3
11
12		OP Ticket		Day	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday
13			Date		10/23	10/24	10/25	10/26	10/27	10/30	10/31	11/1	11/2	11/3	11/6
14			Max Quota
15	Different Hub	Passives
16	Node
17	MDFKD0570D				1				1
18	MDMED02S0C				1						1
19	MDDSD06V0A				1								1
20	MDDSD02R0C	Y			2	2				1
21	MDFKD03J0A	Y													1
TEST Template

MrExcel_20231024 (version 1) (version 1).xlsx
	A	B	C
1	Scheduled Date	HUB Name	Child Node
2	10/23	HUB Location	VAALD09D0A
3	10/23	HUB Location	VAALD0210A
4	10/23	HUB Location	VAALD0100D
5	10/23	HUB Location	VAALD02M0C
6	10/23	HUB Location	VAALD0520C
7	10/23	Different Hub	MDFKD0570D
8	10/23	Different Hub	MDMED02S0C
9	10/23	Different Hub	MDDSD06V0A
10	10/23	Different Hub	MDDSD02R0C
11	10/24	HUB Location	VAALD09D0A
12	10/24	HUB Location	VAALD0210A
13	10/24	Different Hub	MDDSD02R0C
14	10/25	HUB Location	VAALD09D0A
15	10/25	HUB Location	VAALD0210A
16	10/25	HUB Location	VAALD0100D
17	10/26	HUB Location	VAALD0520C
18	10/27	HUB Location	VAALD02M0C
19	10/27	Different Hub	MDFKD0570D
20	10/30	Different Hub	MDDSD02R0C
21	10/31	HUB Location	VAALD09D0A
22	10/31	Different Hub	MDMED02S0C
23	11/1	HUB Location	VAALD09D0A
24	11/2	Different Hub	MDDSD06V0A
25	11/3	HUB Location	VAALD09D0A
26	11/6	Different Hub	MDFKD03J0A
TEST Daily Template

Cell Formulas
Range	Formula
A2:C26	A2	=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.

 

[Dclark015]

Even better and it works great. Thank you again not only for your help with the formula but also for the explanation behind it. I am still in the novice stage of formula writing obviously and trying to learn as much as I can.