LET/VSTACK/FILTER - additional functionality question

[Jedi Master]

I am using a formula to create a filtered array from potentially 50 different sources. It is working perfectly, but I now realize I need to somehow identify if there are "duplicates" on different sheets and only include unique values in the final array, but if a duplicate is identified, I need to add its "Days or Hours used" and "In use hours" values to the unique value in the final array. The formula I am using now is:

=IF('Quote Task Worksheet '!A50="","",LET(X,VSTACK('Quote Task Worksheet '!A50:K73,'Quote Task Worksheet (2)'!A50:K73,'Quote Task Worksheet (3)'!A50:K73,'Quote Task Worksheet (4)'!A50:K73,'Quote Task Worksheet (5)'!A50:K73,'Quote Task Worksheet (6)'!A50:K73,'Quote Task Worksheet (7)'!A50:K73,'Quote Task Worksheet (8)'!A50:K73,'Quote Task Worksheet (9)'!A50:K73,'Quote Task Worksheet (10)'!A50:K73,'Quote Task Worksheet (11)'!A50:K73,'Quote Task Worksheet (12)'!A50:K73,'Quote Task Worksheet (13)'!A50:K73,'Quote Task Worksheet (14)'!A50:K73,'Quote Task Worksheet (15)'!A50:K73,'Quote Task Worksheet (16)'!A50:K73,'Quote Task Worksheet (17)'!A50:K73,'Quote Task Worksheet (18)'!A50:K73,'Quote Task Worksheet (19)'!A50:K73,'Quote Task Worksheet (20)'!A50:K73,'Quote Task Worksheet (21)'!A50:K73,'Quote Task Worksheet (22)'!A50:K73,'Quote Task Worksheet (23)'!A50:K73,'Quote Task Worksheet (24)'!A50:K73,'Quote Task Worksheet (25)'!A50:K73,'Quote Task Worksheet (26)'!A50:K73,'Quote Task Worksheet (27)'!A50:K73,'Quote Task Worksheet (28)'!A50:K73,'Quote Task Worksheet (29)'!A50:K73,'Quote Task Worksheet (30)'!A50:K73,'Quote Task Worksheet (31)'!A50:K73,'Quote Task Worksheet (32)'!A50:K73,'Quote Task Worksheet (33)'!A50:K73,'Quote Task Worksheet (34)'!A50:K73,'Quote Task Worksheet (35)'!A50:K73,'Quote Task Worksheet (36)'!A50:K73,'Quote Task Worksheet (37)'!A50:K73,'Quote Task Worksheet (38)'!A50:K73,'Quote Task Worksheet (39)'!A50:K73,'Quote Task Worksheet (40)'!A50:K73,'Quote Task Worksheet (41)'!A50:K73,'Quote Task Worksheet (42)'!A50:K73,'Quote Task Worksheet (43)'!A50:K73,'Quote Task Worksheet (44)'!A50:K73,'Quote Task Worksheet (45)'!A50:K73,'Quote Task Worksheet (46)'!A50:K73,'Quote Task Worksheet (47)'!A50:K73,'Quote Task Worksheet (48)'!A50:K73,'Quote Task Worksheet (49)'!A50:K73,'Quote Task Worksheet (50)'!A50:K73),FILTER(X,INDEX(X,,1)<>"")))

 

[Vogateer]

I think people will need some indication as to what your structure is like, so we can know which column has the Days or Hours used, and which column would determine that a row is a duplicate.

 

[Jedi Master]

I am using a formula to create a filtered array from potentially 50 different sources. It is working perfectly, but I now realize I need to somehow identify if there are "duplicates" on different sheets and only include unique values in the final array, but if a duplicate is identified, I need to add its "Days or Hours used" and "In use hours" values to the unique value in the final array. The formula I am using now is:

=IF('Quote Task Worksheet '!A50="","",LET(X,VSTACK('Quote Task Worksheet '!A50:K73,'Quote Task Worksheet (2)'!A50:K73,'Quote Task Worksheet (3)'!A50:K73,'Quote Task Worksheet (4)'!A50:K73,'Quote Task Worksheet (5)'!A50:K73,'Quote Task Worksheet (6)'!A50:K73,'Quote Task Worksheet (7)'!A50:K73,'Quote Task Worksheet (8)'!A50:K73,'Quote Task Worksheet (9)'!A50:K73,'Quote Task Worksheet (10)'!A50:K73,'Quote Task Worksheet (11)'!A50:K73,'Quote Task Worksheet (12)'!A50:K73,'Quote Task Worksheet (13)'!A50:K73,'Quote Task Worksheet (14)'!A50:K73,'Quote Task Worksheet (15)'!A50:K73,'Quote Task Worksheet (16)'!A50:K73,'Quote Task Worksheet (17)'!A50:K73,'Quote Task Worksheet (18)'!A50:K73,'Quote Task Worksheet (19)'!A50:K73,'Quote Task Worksheet (20)'!A50:K73,'Quote Task Worksheet (21)'!A50:K73,'Quote Task Worksheet (22)'!A50:K73,'Quote Task Worksheet (23)'!A50:K73,'Quote Task Worksheet (24)'!A50:K73,'Quote Task Worksheet (25)'!A50:K73,'Quote Task Worksheet (26)'!A50:K73,'Quote Task Worksheet (27)'!A50:K73,'Quote Task Worksheet (28)'!A50:K73,'Quote Task Worksheet (29)'!A50:K73,'Quote Task Worksheet (30)'!A50:K73,'Quote Task Worksheet (31)'!A50:K73,'Quote Task Worksheet (32)'!A50:K73,'Quote Task Worksheet (33)'!A50:K73,'Quote Task Worksheet (34)'!A50:K73,'Quote Task Worksheet (35)'!A50:K73,'Quote Task Worksheet (36)'!A50:K73,'Quote Task Worksheet (37)'!A50:K73,'Quote Task Worksheet (38)'!A50:K73,'Quote Task Worksheet (39)'!A50:K73,'Quote Task Worksheet (40)'!A50:K73,'Quote Task Worksheet (41)'!A50:K73,'Quote Task Worksheet (42)'!A50:K73,'Quote Task Worksheet (43)'!A50:K73,'Quote Task Worksheet (44)'!A50:K73,'Quote Task Worksheet (45)'!A50:K73,'Quote Task Worksheet (46)'!A50:K73,'Quote Task Worksheet (47)'!A50:K73,'Quote Task Worksheet (48)'!A50:K73,'Quote Task Worksheet (49)'!A50:K73,'Quote Task Worksheet (50)'!A50:K73),FILTER(X,INDEX(X,,1)<>"")))

I think people will need some indication as to what your structure is like, so we can know which column has the Days or Hours used, and which column would determine that a row is a duplicate.

Yes, my picture file was too large, I am trying to send the rest of the info now

 

[Jedi Master]

Yes, my picture file was too large, I am trying to send the rest of the info now

Here is the rest of the data visualization

 

[Vogateer]

How did you want to add the days and hours together? Seems like it would make things a lot easier if you just used days or hours as your standard unit so you can add them together.

 

[Jedi Master]

How did you want to add the days and hours together? Seems like it would make things a lot easier if you just used days or hours as your standard unit so you can add them together.

Does this give a more precise understanding?

 

[Jedi Master]

Does this give a more precise understanding?

Agreed, even though it says days or hours, it is just days

 

[Vogateer]

I'm sure the gurus in here would have better formulas, but this appears to work for me with the data I made up that resembles what you have. I only filled out a little bit to check things out, so if you want me to try it with your actual data, you'll have to post it here in some way that I can copy and paste it into my sheet.

Here's a quick image of my "Summary" sheet for reference, where I tried to stick to the layout in one of your images:

A3 formula (which takes care of the first three columns):

=IF('Quote Task Worksheet '!A50="","",LET(X,VSTACK('Quote Task Worksheet '!A50:C73,'Quote Task Worksheet (2)'!A50:C73,'Quote Task Worksheet (3)'!A50:C73,'Quote Task Worksheet (4)'!A50:C73,'Quote Task Worksheet (5)'!A50:C73,'Quote Task Worksheet (6)'!A50:C73,'Quote Task Worksheet (7)'!A50:C73,'Quote Task Worksheet (8)'!A50:C73,'Quote Task Worksheet (9)'!A50:C73,'Quote Task Worksheet (10)'!A50:C73,'Quote Task Worksheet (11)'!A50:C73,'Quote Task Worksheet (12)'!A50:C73,'Quote Task Worksheet (13)'!A50:C73,'Quote Task Worksheet (14)'!A50:C73,'Quote Task Worksheet (15)'!A50:C73,'Quote Task Worksheet (16)'!A50:C73,'Quote Task Worksheet (17)'!A50:C73,'Quote Task Worksheet (18)'!A50:C73,'Quote Task Worksheet (19)'!A50:C73,'Quote Task Worksheet (20)'!A50:C73,'Quote Task Worksheet (21)'!A50:C73,'Quote Task Worksheet (22)'!A50:C73,'Quote Task Worksheet (23)'!A50:C73,'Quote Task Worksheet (24)'!A50:C73,'Quote Task Worksheet (25)'!A50:C73,'Quote Task Worksheet (26)'!A50:C73,'Quote Task Worksheet (27)'!A50:C73,'Quote Task Worksheet (28)'!A50:C73,'Quote Task Worksheet (29)'!A50:C73,'Quote Task Worksheet (30)'!A50:C73,'Quote Task Worksheet (31)'!A50:C73,'Quote Task Worksheet (32)'!A50:C73,'Quote Task Worksheet (33)'!A50:C73,'Quote Task Worksheet (34)'!A50:C73,'Quote Task Worksheet (35)'!A50:C73,'Quote Task Worksheet (36)'!A50:C73,'Quote Task Worksheet (37)'!A50:C73,'Quote Task Worksheet (38)'!A50:C73,'Quote Task Worksheet (39)'!A50:C73,'Quote Task Worksheet (40)'!A50:C73,'Quote Task Worksheet (41)'!A50:C73,'Quote Task Worksheet (42)'!A50:C73,'Quote Task Worksheet (43)'!A50:C73,'Quote Task Worksheet (44)'!A50:C73,'Quote Task Worksheet (45)'!A50:C73,'Quote Task Worksheet (46)'!A50:C73,'Quote Task Worksheet (47)'!A50:C73,'Quote Task Worksheet (48)'!A50:C73,'Quote Task Worksheet (49)'!A50:C73,'Quote Task Worksheet (50)'!A50:C73),UNIQUE(FILTER(X,INDEX(X,,1)<>""))))

D3 (carry this formula down as far as you need to):

=IF($A3="","",LET(X,VSTACK('Quote Task Worksheet '!$A$50:$K$73,'Quote Task Worksheet (2)'!$A$50:$K$73,'Quote Task Worksheet (3)'!$A$50:$K$73,'Quote Task Worksheet (4)'!$A$50:$K$73,'Quote Task Worksheet (5)'!$A$50:$K$73,'Quote Task Worksheet (6)'!$A$50:$K$73,'Quote Task Worksheet (7)'!$A$50:$K$73,'Quote Task Worksheet (8)'!$A$50:$K$73,'Quote Task Worksheet (9)'!$A$50:$K$73,'Quote Task Worksheet (10)'!$A$50:$K$73,'Quote Task Worksheet (11)'!$A$50:$K$73,'Quote Task Worksheet (12)'!$A$50:$K$73,'Quote Task Worksheet (13)'!$A$50:$K$73,'Quote Task Worksheet (14)'!$A$50:$K$73,'Quote Task Worksheet (15)'!$A$50:$K$73,'Quote Task Worksheet (16)'!$A$50:$K$73,'Quote Task Worksheet (17)'!$A$50:$K$73,'Quote Task Worksheet (18)'!$A$50:$K$73,'Quote Task Worksheet (19)'!$A$50:$K$73,'Quote Task Worksheet (20)'!$A$50:$K$73,'Quote Task Worksheet (21)'!$A$50:$K$73,'Quote Task Worksheet (22)'!$A$50:$K$73,'Quote Task Worksheet (23)'!$A$50:$K$73,'Quote Task Worksheet (24)'!$A$50:$K$73,'Quote Task Worksheet (25)'!$A$50:$K$73,'Quote Task Worksheet (26)'!$A$50:$K$73,'Quote Task Worksheet (27)'!$A$50:$K$73,'Quote Task Worksheet (28)'!$A$50:$K$73,'Quote Task Worksheet (29)'!$A$50:$K$73,'Quote Task Worksheet (30)'!$A$50:$K$73,'Quote Task Worksheet (31)'!$A$50:$K$73,'Quote Task Worksheet (32)'!$A$50:$K$73,'Quote Task Worksheet (33)'!$A$50:$K$73,'Quote Task Worksheet (34)'!$A$50:$K$73,'Quote Task Worksheet (35)'!$A$50:$K$73,'Quote Task Worksheet (36)'!$A$50:$K$73,'Quote Task Worksheet (37)'!$A$50:$K$73,'Quote Task Worksheet (38)'!$A$50:$K$73,'Quote Task Worksheet (39)'!$A$50:$K$73,'Quote Task Worksheet (40)'!$A$50:$K$73,'Quote Task Worksheet (41)'!$A$50:$K$73,'Quote Task Worksheet (42)'!$A$50:$K$73,'Quote Task Worksheet (43)'!$A$50:$K$73,'Quote Task Worksheet (44)'!$A$50:$K$73,'Quote Task Worksheet (45)'!$A$50:$K$73,'Quote Task Worksheet (46)'!$A$50:$K$73,'Quote Task Worksheet (47)'!$A$50:$K$73,'Quote Task Worksheet (48)'!$A$50:$K$73,'Quote Task Worksheet (49)'!$A$50:$K$73,'Quote Task Worksheet (50)'!$A$50:$K$73),SUM(FILTER(CHOOSECOLS(X,4), CHOOSECOLS(X,1)=$A3))))

E5 (through I5 if you increase the column number in the first CHOOSECOLS formula by one for each column):

=IF($A3="","",LET(X,VSTACK('Quote Task Worksheet '!$A$50:$K$73,'Quote Task Worksheet (2)'!$A$50:$K$73,'Quote Task Worksheet (3)'!$A$50:$K$73,'Quote Task Worksheet (4)'!$A$50:$K$73,'Quote Task Worksheet (5)'!$A$50:$K$73,'Quote Task Worksheet (6)'!$A$50:$K$73,'Quote Task Worksheet (7)'!$A$50:$K$73,'Quote Task Worksheet (8)'!$A$50:$K$73,'Quote Task Worksheet (9)'!$A$50:$K$73,'Quote Task Worksheet (10)'!$A$50:$K$73,'Quote Task Worksheet (11)'!$A$50:$K$73,'Quote Task Worksheet (12)'!$A$50:$K$73,'Quote Task Worksheet (13)'!$A$50:$K$73,'Quote Task Worksheet (14)'!$A$50:$K$73,'Quote Task Worksheet (15)'!$A$50:$K$73,'Quote Task Worksheet (16)'!$A$50:$K$73,'Quote Task Worksheet (17)'!$A$50:$K$73,'Quote Task Worksheet (18)'!$A$50:$K$73,'Quote Task Worksheet (19)'!$A$50:$K$73,'Quote Task Worksheet (20)'!$A$50:$K$73,'Quote Task Worksheet (21)'!$A$50:$K$73,'Quote Task Worksheet (22)'!$A$50:$K$73,'Quote Task Worksheet (23)'!$A$50:$K$73,'Quote Task Worksheet (24)'!$A$50:$K$73,'Quote Task Worksheet (25)'!$A$50:$K$73,'Quote Task Worksheet (26)'!$A$50:$K$73,'Quote Task Worksheet (27)'!$A$50:$K$73,'Quote Task Worksheet (28)'!$A$50:$K$73,'Quote Task Worksheet (29)'!$A$50:$K$73,'Quote Task Worksheet (30)'!$A$50:$K$73,'Quote Task Worksheet (31)'!$A$50:$K$73,'Quote Task Worksheet (32)'!$A$50:$K$73,'Quote Task Worksheet (33)'!$A$50:$K$73,'Quote Task Worksheet (34)'!$A$50:$K$73,'Quote Task Worksheet (35)'!$A$50:$K$73,'Quote Task Worksheet (36)'!$A$50:$K$73,'Quote Task Worksheet (37)'!$A$50:$K$73,'Quote Task Worksheet (38)'!$A$50:$K$73,'Quote Task Worksheet (39)'!$A$50:$K$73,'Quote Task Worksheet (40)'!$A$50:$K$73,'Quote Task Worksheet (41)'!$A$50:$K$73,'Quote Task Worksheet (42)'!$A$50:$K$73,'Quote Task Worksheet (43)'!$A$50:$K$73,'Quote Task Worksheet (44)'!$A$50:$K$73,'Quote Task Worksheet (45)'!$A$50:$K$73,'Quote Task Worksheet (46)'!$A$50:$K$73,'Quote Task Worksheet (47)'!$A$50:$K$73,'Quote Task Worksheet (48)'!$A$50:$K$73,'Quote Task Worksheet (49)'!$A$50:$K$73,'Quote Task Worksheet (50)'!$A$50:$K$73),INDEX(CHOOSECOLS(X,5), MATCH($A3,CHOOSECOLS(X,1),0))))

J5 (carried down as far as you need, an example that increments the CHOOSECOLS number from formula in D3)

=IF($A3="","",LET(X,VSTACK('Quote Task Worksheet '!$A$50:$K$73,'Quote Task Worksheet (2)'!$A$50:$K$73,'Quote Task Worksheet (3)'!$A$50:$K$73,'Quote Task Worksheet (4)'!$A$50:$K$73,'Quote Task Worksheet (5)'!$A$50:$K$73,'Quote Task Worksheet (6)'!$A$50:$K$73,'Quote Task Worksheet (7)'!$A$50:$K$73,'Quote Task Worksheet (8)'!$A$50:$K$73,'Quote Task Worksheet (9)'!$A$50:$K$73,'Quote Task Worksheet (10)'!$A$50:$K$73,'Quote Task Worksheet (11)'!$A$50:$K$73,'Quote Task Worksheet (12)'!$A$50:$K$73,'Quote Task Worksheet (13)'!$A$50:$K$73,'Quote Task Worksheet (14)'!$A$50:$K$73,'Quote Task Worksheet (15)'!$A$50:$K$73,'Quote Task Worksheet (16)'!$A$50:$K$73,'Quote Task Worksheet (17)'!$A$50:$K$73,'Quote Task Worksheet (18)'!$A$50:$K$73,'Quote Task Worksheet (19)'!$A$50:$K$73,'Quote Task Worksheet (20)'!$A$50:$K$73,'Quote Task Worksheet (21)'!$A$50:$K$73,'Quote Task Worksheet (22)'!$A$50:$K$73,'Quote Task Worksheet (23)'!$A$50:$K$73,'Quote Task Worksheet (24)'!$A$50:$K$73,'Quote Task Worksheet (25)'!$A$50:$K$73,'Quote Task Worksheet (26)'!$A$50:$K$73,'Quote Task Worksheet (27)'!$A$50:$K$73,'Quote Task Worksheet (28)'!$A$50:$K$73,'Quote Task Worksheet (29)'!$A$50:$K$73,'Quote Task Worksheet (30)'!$A$50:$K$73,'Quote Task Worksheet (31)'!$A$50:$K$73,'Quote Task Worksheet (32)'!$A$50:$K$73,'Quote Task Worksheet (33)'!$A$50:$K$73,'Quote Task Worksheet (34)'!$A$50:$K$73,'Quote Task Worksheet (35)'!$A$50:$K$73,'Quote Task Worksheet (36)'!$A$50:$K$73,'Quote Task Worksheet (37)'!$A$50:$K$73,'Quote Task Worksheet (38)'!$A$50:$K$73,'Quote Task Worksheet (39)'!$A$50:$K$73,'Quote Task Worksheet (40)'!$A$50:$K$73,'Quote Task Worksheet (41)'!$A$50:$K$73,'Quote Task Worksheet (42)'!$A$50:$K$73,'Quote Task Worksheet (43)'!$A$50:$K$73,'Quote Task Worksheet (44)'!$A$50:$K$73,'Quote Task Worksheet (45)'!$A$50:$K$73,'Quote Task Worksheet (46)'!$A$50:$K$73,'Quote Task Worksheet (47)'!$A$50:$K$73,'Quote Task Worksheet (48)'!$A$50:$K$73,'Quote Task Worksheet (49)'!$A$50:$K$73,'Quote Task Worksheet (50)'!$A$50:$K$73),SUM(FILTER(CHOOSECOLS(X,10), CHOOSECOLS(X,1)=$A3))))

 

[Jedi Master]

I'm sure the gurus in here would have better formulas, but this appears to work for me with the data I made up that resembles what you have. I only filled out a little bit to check things out, so if you want me to try it with your actual data, you'll have to post it here in some way that I can copy and paste it into my sheet.

Here's a quick image of my "Summary" sheet for reference, where I tried to stick to the layout in one of your images:
View attachment 113963

A3 formula (which takes care of the first three columns):

=IF('Quote Task Worksheet '!A50="","",LET(X,VSTACK('Quote Task Worksheet '!A50:C73,'Quote Task Worksheet (2)'!A50:C73,'Quote Task Worksheet (3)'!A50:C73,'Quote Task Worksheet (4)'!A50:C73,'Quote Task Worksheet (5)'!A50:C73,'Quote Task Worksheet (6)'!A50:C73,'Quote Task Worksheet (7)'!A50:C73,'Quote Task Worksheet (8)'!A50:C73,'Quote Task Worksheet (9)'!A50:C73,'Quote Task Worksheet (10)'!A50:C73,'Quote Task Worksheet (11)'!A50:C73,'Quote Task Worksheet (12)'!A50:C73,'Quote Task Worksheet (13)'!A50:C73,'Quote Task Worksheet (14)'!A50:C73,'Quote Task Worksheet (15)'!A50:C73,'Quote Task Worksheet (16)'!A50:C73,'Quote Task Worksheet (17)'!A50:C73,'Quote Task Worksheet (18)'!A50:C73,'Quote Task Worksheet (19)'!A50:C73,'Quote Task Worksheet (20)'!A50:C73,'Quote Task Worksheet (21)'!A50:C73,'Quote Task Worksheet (22)'!A50:C73,'Quote Task Worksheet (23)'!A50:C73,'Quote Task Worksheet (24)'!A50:C73,'Quote Task Worksheet (25)'!A50:C73,'Quote Task Worksheet (26)'!A50:C73,'Quote Task Worksheet (27)'!A50:C73,'Quote Task Worksheet (28)'!A50:C73,'Quote Task Worksheet (29)'!A50:C73,'Quote Task Worksheet (30)'!A50:C73,'Quote Task Worksheet (31)'!A50:C73,'Quote Task Worksheet (32)'!A50:C73,'Quote Task Worksheet (33)'!A50:C73,'Quote Task Worksheet (34)'!A50:C73,'Quote Task Worksheet (35)'!A50:C73,'Quote Task Worksheet (36)'!A50:C73,'Quote Task Worksheet (37)'!A50:C73,'Quote Task Worksheet (38)'!A50:C73,'Quote Task Worksheet (39)'!A50:C73,'Quote Task Worksheet (40)'!A50:C73,'Quote Task Worksheet (41)'!A50:C73,'Quote Task Worksheet (42)'!A50:C73,'Quote Task Worksheet (43)'!A50:C73,'Quote Task Worksheet (44)'!A50:C73,'Quote Task Worksheet (45)'!A50:C73,'Quote Task Worksheet (46)'!A50:C73,'Quote Task Worksheet (47)'!A50:C73,'Quote Task Worksheet (48)'!A50:C73,'Quote Task Worksheet (49)'!A50:C73,'Quote Task Worksheet (50)'!A50:C73),UNIQUE(FILTER(X,INDEX(X,,1)<>""))))

D3 (carry this formula down as far as you need to):

=IF($A3="","",LET(X,VSTACK('Quote Task Worksheet '!$A$50:$K$73,'Quote Task Worksheet (2)'!$A$50:$K$73,'Quote Task Worksheet (3)'!$A$50:$K$73,'Quote Task Worksheet (4)'!$A$50:$K$73,'Quote Task Worksheet (5)'!$A$50:$K$73,'Quote Task Worksheet (6)'!$A$50:$K$73,'Quote Task Worksheet (7)'!$A$50:$K$73,'Quote Task Worksheet (8)'!$A$50:$K$73,'Quote Task Worksheet (9)'!$A$50:$K$73,'Quote Task Worksheet (10)'!$A$50:$K$73,'Quote Task Worksheet (11)'!$A$50:$K$73,'Quote Task Worksheet (12)'!$A$50:$K$73,'Quote Task Worksheet (13)'!$A$50:$K$73,'Quote Task Worksheet (14)'!$A$50:$K$73,'Quote Task Worksheet (15)'!$A$50:$K$73,'Quote Task Worksheet (16)'!$A$50:$K$73,'Quote Task Worksheet (17)'!$A$50:$K$73,'Quote Task Worksheet (18)'!$A$50:$K$73,'Quote Task Worksheet (19)'!$A$50:$K$73,'Quote Task Worksheet (20)'!$A$50:$K$73,'Quote Task Worksheet (21)'!$A$50:$K$73,'Quote Task Worksheet (22)'!$A$50:$K$73,'Quote Task Worksheet (23)'!$A$50:$K$73,'Quote Task Worksheet (24)'!$A$50:$K$73,'Quote Task Worksheet (25)'!$A$50:$K$73,'Quote Task Worksheet (26)'!$A$50:$K$73,'Quote Task Worksheet (27)'!$A$50:$K$73,'Quote Task Worksheet (28)'!$A$50:$K$73,'Quote Task Worksheet (29)'!$A$50:$K$73,'Quote Task Worksheet (30)'!$A$50:$K$73,'Quote Task Worksheet (31)'!$A$50:$K$73,'Quote Task Worksheet (32)'!$A$50:$K$73,'Quote Task Worksheet (33)'!$A$50:$K$73,'Quote Task Worksheet (34)'!$A$50:$K$73,'Quote Task Worksheet (35)'!$A$50:$K$73,'Quote Task Worksheet (36)'!$A$50:$K$73,'Quote Task Worksheet (37)'!$A$50:$K$73,'Quote Task Worksheet (38)'!$A$50:$K$73,'Quote Task Worksheet (39)'!$A$50:$K$73,'Quote Task Worksheet (40)'!$A$50:$K$73,'Quote Task Worksheet (41)'!$A$50:$K$73,'Quote Task Worksheet (42)'!$A$50:$K$73,'Quote Task Worksheet (43)'!$A$50:$K$73,'Quote Task Worksheet (44)'!$A$50:$K$73,'Quote Task Worksheet (45)'!$A$50:$K$73,'Quote Task Worksheet (46)'!$A$50:$K$73,'Quote Task Worksheet (47)'!$A$50:$K$73,'Quote Task Worksheet (48)'!$A$50:$K$73,'Quote Task Worksheet (49)'!$A$50:$K$73,'Quote Task Worksheet (50)'!$A$50:$K$73),SUM(FILTER(CHOOSECOLS(X,4), CHOOSECOLS(X,1)=$A3))))

E5 (through I5 if you increase the column number in the first CHOOSECOLS formula by one for each column):

=IF($A3="","",LET(X,VSTACK('Quote Task Worksheet '!$A$50:$K$73,'Quote Task Worksheet (2)'!$A$50:$K$73,'Quote Task Worksheet (3)'!$A$50:$K$73,'Quote Task Worksheet (4)'!$A$50:$K$73,'Quote Task Worksheet (5)'!$A$50:$K$73,'Quote Task Worksheet (6)'!$A$50:$K$73,'Quote Task Worksheet (7)'!$A$50:$K$73,'Quote Task Worksheet (8)'!$A$50:$K$73,'Quote Task Worksheet (9)'!$A$50:$K$73,'Quote Task Worksheet (10)'!$A$50:$K$73,'Quote Task Worksheet (11)'!$A$50:$K$73,'Quote Task Worksheet (12)'!$A$50:$K$73,'Quote Task Worksheet (13)'!$A$50:$K$73,'Quote Task Worksheet (14)'!$A$50:$K$73,'Quote Task Worksheet (15)'!$A$50:$K$73,'Quote Task Worksheet (16)'!$A$50:$K$73,'Quote Task Worksheet (17)'!$A$50:$K$73,'Quote Task Worksheet (18)'!$A$50:$K$73,'Quote Task Worksheet (19)'!$A$50:$K$73,'Quote Task Worksheet (20)'!$A$50:$K$73,'Quote Task Worksheet (21)'!$A$50:$K$73,'Quote Task Worksheet (22)'!$A$50:$K$73,'Quote Task Worksheet (23)'!$A$50:$K$73,'Quote Task Worksheet (24)'!$A$50:$K$73,'Quote Task Worksheet (25)'!$A$50:$K$73,'Quote Task Worksheet (26)'!$A$50:$K$73,'Quote Task Worksheet (27)'!$A$50:$K$73,'Quote Task Worksheet (28)'!$A$50:$K$73,'Quote Task Worksheet (29)'!$A$50:$K$73,'Quote Task Worksheet (30)'!$A$50:$K$73,'Quote Task Worksheet (31)'!$A$50:$K$73,'Quote Task Worksheet (32)'!$A$50:$K$73,'Quote Task Worksheet (33)'!$A$50:$K$73,'Quote Task Worksheet (34)'!$A$50:$K$73,'Quote Task Worksheet (35)'!$A$50:$K$73,'Quote Task Worksheet (36)'!$A$50:$K$73,'Quote Task Worksheet (37)'!$A$50:$K$73,'Quote Task Worksheet (38)'!$A$50:$K$73,'Quote Task Worksheet (39)'!$A$50:$K$73,'Quote Task Worksheet (40)'!$A$50:$K$73,'Quote Task Worksheet (41)'!$A$50:$K$73,'Quote Task Worksheet (42)'!$A$50:$K$73,'Quote Task Worksheet (43)'!$A$50:$K$73,'Quote Task Worksheet (44)'!$A$50:$K$73,'Quote Task Worksheet (45)'!$A$50:$K$73,'Quote Task Worksheet (46)'!$A$50:$K$73,'Quote Task Worksheet (47)'!$A$50:$K$73,'Quote Task Worksheet (48)'!$A$50:$K$73,'Quote Task Worksheet (49)'!$A$50:$K$73,'Quote Task Worksheet (50)'!$A$50:$K$73),INDEX(CHOOSECOLS(X,5), MATCH($A3,CHOOSECOLS(X,1),0))))

J5 (carried down as far as you need, an example that increments the CHOOSECOLS number from formula in D3)

=IF($A3="","",LET(X,VSTACK('Quote Task Worksheet '!$A$50:$K$73,'Quote Task Worksheet (2)'!$A$50:$K$73,'Quote Task Worksheet (3)'!$A$50:$K$73,'Quote Task Worksheet (4)'!$A$50:$K$73,'Quote Task Worksheet (5)'!$A$50:$K$73,'Quote Task Worksheet (6)'!$A$50:$K$73,'Quote Task Worksheet (7)'!$A$50:$K$73,'Quote Task Worksheet (8)'!$A$50:$K$73,'Quote Task Worksheet (9)'!$A$50:$K$73,'Quote Task Worksheet (10)'!$A$50:$K$73,'Quote Task Worksheet (11)'!$A$50:$K$73,'Quote Task Worksheet (12)'!$A$50:$K$73,'Quote Task Worksheet (13)'!$A$50:$K$73,'Quote Task Worksheet (14)'!$A$50:$K$73,'Quote Task Worksheet (15)'!$A$50:$K$73,'Quote Task Worksheet (16)'!$A$50:$K$73,'Quote Task Worksheet (17)'!$A$50:$K$73,'Quote Task Worksheet (18)'!$A$50:$K$73,'Quote Task Worksheet (19)'!$A$50:$K$73,'Quote Task Worksheet (20)'!$A$50:$K$73,'Quote Task Worksheet (21)'!$A$50:$K$73,'Quote Task Worksheet (22)'!$A$50:$K$73,'Quote Task Worksheet (23)'!$A$50:$K$73,'Quote Task Worksheet (24)'!$A$50:$K$73,'Quote Task Worksheet (25)'!$A$50:$K$73,'Quote Task Worksheet (26)'!$A$50:$K$73,'Quote Task Worksheet (27)'!$A$50:$K$73,'Quote Task Worksheet (28)'!$A$50:$K$73,'Quote Task Worksheet (29)'!$A$50:$K$73,'Quote Task Worksheet (30)'!$A$50:$K$73,'Quote Task Worksheet (31)'!$A$50:$K$73,'Quote Task Worksheet (32)'!$A$50:$K$73,'Quote Task Worksheet (33)'!$A$50:$K$73,'Quote Task Worksheet (34)'!$A$50:$K$73,'Quote Task Worksheet (35)'!$A$50:$K$73,'Quote Task Worksheet (36)'!$A$50:$K$73,'Quote Task Worksheet (37)'!$A$50:$K$73,'Quote Task Worksheet (38)'!$A$50:$K$73,'Quote Task Worksheet (39)'!$A$50:$K$73,'Quote Task Worksheet (40)'!$A$50:$K$73,'Quote Task Worksheet (41)'!$A$50:$K$73,'Quote Task Worksheet (42)'!$A$50:$K$73,'Quote Task Worksheet (43)'!$A$50:$K$73,'Quote Task Worksheet (44)'!$A$50:$K$73,'Quote Task Worksheet (45)'!$A$50:$K$73,'Quote Task Worksheet (46)'!$A$50:$K$73,'Quote Task Worksheet (47)'!$A$50:$K$73,'Quote Task Worksheet (48)'!$A$50:$K$73,'Quote Task Worksheet (49)'!$A$50:$K$73,'Quote Task Worksheet (50)'!$A$50:$K$73),SUM(FILTER(CHOOSECOLS(X,10), CHOOSECOLS(X,1)=$A3))))

I think you're on the right track! I am currently trying to figure a version of the same out for myself. You can find the information you requested here: LET/VSTACK/FILTER question

I look forward to your response, and will also let you know what I come up with. Thank you!!

 

[Vogateer]

Here's what I get with those 5 sheets of data: