VSTACK - can it combine specific columns of data

[usnapoleon]

I have a VSTACK formula that I utilize elsewhere and I want to see if it can be adapted for something slightly different. Typically it combines consecutive columns of data, which is what my current formula does. But on my new desired adapted use, I want to pick specific columns to pull data. Is this possible?

Here is an image of what it's doing right now, which you can laugh at the obvious 'not working as intended'

I do want it to pull column B, from the tabs 1-31, but I dont want B-W, I want B, L, R, T, and (this one may be tricky) the numerical sum of columns S less T

Can I make the VSTACK picky like that?

Here's the current formula I have. I didnt make it myself, I think I got it from someone else's spreadsheet in the past.

=LET(Data,VSTACK('1:31'!$B$23:$W$72),column1,TAKE(Data,,1),output,FILTER(Data,column1<>0,""),output)

I normally try to understand what the formula is saying... try to verbalize out loud it into something anyone could understand, and that helps me understand formulas and break them down and make edits to them in the future (like now), but I can't follow it starting with 'column1,TAKE(Data....etc etc'. Consequently I cant edit it myself.

Thank you

 

[Cubist]

The TAKE(data,,1) is grabbing the first column, then filter where it's not 0.
To choose more than 1 column, try replace the TAKE with in CHOOSECOLS(data,1,2,5) to choose column 1,2 and 5 (for example).

 

[usnapoleon]

The TAKE(data,,1) is grabbing the first column, then filter where it's not 0.
To choose more than 1 column, try replace the TAKE with in CHOOSECOLS(data,1,2,5) to choose column 1,2 and 5 (for example).

Hi Cubist! Good seeing you again!

I made the edit:

=LET(Data,VSTACK('1:31'!$B$23:$W$72),column1,CHOOSECOLS(Data,1,11,17,19),output,FILTER(Data,column1<>0,""),output)

is this how you wanted me to edit it? I did the intended columns, with B being column 1, and providing the numerical equivalent for columns B, L R, and T. I know I want columns S and T to be S minus T (so column 18 minus 19) but I figure it's best to get this working before I try to figure out that fanciness.

The result of the above formula came back with a #VALUE, so I know I am doing something wrong. At the bare minimum I hope I edited the right parts! It was showing the number appropriately 333 initially, as shown in my first posting.

 

[Cubist]

Try:

=LET(Data,CHOOSECOLS('1:31'!$B$23:$W$72,1,11,17,19),output,FILTER(Data,INDEX(Data,,1)<>0,""),output)

 

[usnapoleon]

Try:

=LET(Data,CHOOSECOLS('1:31'!$B$23:$W$72,1,11,17,19),output,FILTER(Data,INDEX(Data,,1)<>0,""),output)

Howdy!

This came out with #VALUE too

Just as additional info, here is what is displayed on tab 1,

0125 WRSD Voucher Tracker.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
21			CG	MP	PH	BS	MS	SH	TH	ACT		RW	FV	SB	TH	ACT		Squirrel	Identified
22	Tour ID	Ck #	Canyon Grille	Market Place	Pizza Hut	Boulder Springs	Mtn. Springs	The Shop	Theater	Activities	Dept	Resort Wide	Food Voucher	Starbucks	Theater	Activities	Type	AMOUNT	Variance
23	99	4733									CG						RW	200.00
1

so I expected the results to be
99 CG RW 200

 

[Cubist]

Try:

=LET(Data,CHOOSECOLS(VSTACK('1:31'!$B$23:$W$72),1,11,17,19),output,FILTER(Data,INDEX(Data,,1)<>0,""),output)

 

[usnapoleon]

Try:

=LET(Data,CHOOSECOLS(VSTACK('1:31'!$B$23:$W$72),1,11,17,19),output,FILTER(Data,INDEX(Data,,1)<>0,""),output)

This gave a result of #NAME?

 

[Cubist]

See this.

How to correct a #NAME? error - Microsoft Support

The top reason why the #NAME? error appears in your formula is because there is a typo in the formula name. To avoid typos in formula names, use the Formula Wizard in Excel.

support.microsoft.com

It usually mean you don't have the functions in your current version, but could be other reasons like misspelling a function or missing quotations. Which version of Excel are you using?

Did you copy the formula provided exactly or did you alter it?

 

[usnapoleon]

See this.

How to correct a #NAME? error - Microsoft Support

The top reason why the #NAME? error appears in your formula is because there is a typo in the formula name. To avoid typos in formula names, use the Formula Wizard in Excel.

support.microsoft.com

It usually mean you don't have the functions in your current version, but could be other reasons like misspelling a function or missing quotations. Which version of Excel are you using?

Did you copy the formula provided exactly or did you alter it?

Hello
Sorry for the delay!

I'm using Microsoft 365.

Additionally, I did something stupid and the beginning of the formula was LETLET... wow... I've never made such a botch before, I'm so glad you made me go back and look.

Making the change worked!

Are we able to get fancy now with the amount to be a column 18 minus 19? If it's not possible I can alter your formula so it shows 18 and 19, and then make a new formula directly on the next column that does the math.

I tried this (see below in red), but it didn't work. I wasn't optimistic, but you never know unless you try!

=LET(Data,CHOOSECOLS(VSTACK('1:31'!$B$23:$W$72),1,11,17,(18-19)),output,FILTER(Data,INDEX(Data,,1)<>0,""),output)

Another thing I am unsure of is the date column I have in column A. The formula you've been helping me with starts in column B, because the individual tabs 1-31 dont have the Dates laid out amongst the data that the VSTACK can utilize. The date only exists 1x at the top of the individual tabs (like shown below). I may have to delete column A for Date and hope the higher ups don't need that information. Thoughts?