Adding column to dynamic array

[tdcockers]

Hi all... I've been playing with this for a while, to no avail. I've got a series of budgets for different branches on different tabs. I can pull the relevant information from each tab using FILTER, and combine them into a single export-ready list using VSTACK... but I'm hoping that I can add a column to each FILTER with an arbitrary 3-letter code representing the branch to line to indicate which tab the data is sourced from.

Currently the filter for each branch produces "Product | Account | Budget | Forecast"; I'd like this to have an additional column eg "Branch | Product | Account | Budget | Forecast".

Any suggestions? I could add the codes to a new column in each budget and include that in the filter, but it's just something else for end users to miss or mess up.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What is your current formula?

 

[tdcockers]

Updated, thanks.

Current formula:
=VSTACK(FILTER(FILTER('2023 CLP Budget'!B:J,('2023 CLP Budget'!B:B<>0)),{1,0,1,1,0,1,0,0,1}),FILTER(FILTER('2023 CHY Budget'!B:J,('2023 CHY Budget'!B:B<>0)),{1,0,1,1,0,1,0,0,1}))

I can either add a new column with 'CLP' or 'CHY' in every row, or overwrite one of the columns with that information... I've been fiddling to try and make either method work but haven't gotten there yet.

 

[tdcockers]

Never mind, was nearly there, apparently I just needed to sleep on it. Working formula (happy to hear a tidier way to do this if anyone can point me in the right direction):

=VSTACK(LET(arrDATA, FILTER(FILTER('2023 CLP Budget'!B:J, ('2023 CLP Budget'!B:B <> 0)), {1,0,1,1,0,1,0,0,1}), arrFARM, REPLACE(SEQUENCE(ROWS(arrDATA), 1),1,2,"CLP"), result, HSTACK(arrDATA, arrFARM), result),LET(arrDATA, FILTER(FILTER('2023 CHY Budget'!B:J, ('2023 CHY Budget'!B:B <> 0)), {1,0,1,1,0,1,0,0,1}), arrFARM, REPLACE(SEQUENCE(ROWS(arrDATA), 1),1,2,"CHY"), result, HSTACK(arrDATA, arrFARM), result))

 

[tdcockers]

Note to future self and others... of course PowerQuery was a much smarter way to achieve the outcome I was looking for.

 

[Fluff]

Glad you sorted it & thanks for letting us know.