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)<>"")))

 

[Jedi Master]

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

That looks correct. Is that using your response previous?

 

[Vogateer]

That looks correct. Is that using your response previous?

Yes, I didn't change anything in the formulas, just the data from the sheets you posted.

I don't think you'll have any issues modifying the formulas for the columns I didn't post, because it sounds like you understand the functions used in these formulas, but I can post those other formulas if they do happen to give you any trouble.

 

[Jedi Master]

Yes, I didn't change anything in the formulas, just the data from the sheets you posted.

I don't think you'll have any issues modifying the formulas for the columns I didn't post, because it sounds like you understand the functions used in these formulas, but I can post those other formulas if they do happen to give you any trouble.

When I paste the first formula in the 'Quote Task Master' worksheet in cell A50, I get the above window 50 times, for all referenced sheets? When I cancel out all 50 windows, I then get a #REF error

 

[Vogateer]

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

It will do that if the Sheet Names aren't exactly correct. Did you happen to have extra spaces in your worksheet names? In the formula of your first post, I see that there's a space after the name of the first worksheet. There might be two spaces in the other worksheet names that didn't carry over because it was posted into the forum without the formula code. You might need to do a search and replace in the formulas to get them to perfectly match your sheet names.

 

[Jedi Master]

It will do that if the Sheet Names aren't exactly correct. Did you happen to have extra spaces in your worksheet names? In the formula of your first post, I see that there's a space after the name of the first worksheet. There might be two spaces in the other worksheet names that didn't carry over because it was posted into the forum without the formula code. You might need to do a search and replace in the formulas to get them to perfectly match your sheet names.

Thank you so much! That was it.