VSTACK - can it combine specific columns of data

usnapoleon

Board Regular
Joined
May 22, 2014
Messages
115
Office Version
  1. 365
Platform
  1. Windows
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'

1737512078069.png


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
 
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).
 
Last edited:
Upvote 0
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.
 
Upvote 0
Try:
Excel Formula:
=LET(Data,CHOOSECOLS('1:31'!$B$23:$W$72,1,11,17,19),output,FILTER(Data,INDEX(Data,,1)<>0,""),output)
 
Upvote 0
Try:
Excel Formula:
=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
BCDEFGHIJKLMNOPQRST
21CGMPPHBSMSSHTHACTRWFVSBTHACTSquirrelIdentified
22Tour IDCk #Canyon GrilleMarket PlacePizza HutBoulder SpringsMtn. SpringsThe ShopTheaterActivitiesDeptResort WideFood VoucherStarbucksTheaterActivitiesTypeAMOUNTVariance
23994733CGRW200.00
1


so I expected the results to be
99 CG RW 200
 
Upvote 0
Try:
Excel Formula:
=LET(Data,CHOOSECOLS(VSTACK('1:31'!$B$23:$W$72),1,11,17,19),output,FILTER(Data,INDEX(Data,,1)<>0,""),output)
 
Upvote 0
Solution
See this.

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?
 
Upvote 0
See this.

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!

1737662386511.png


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?

1737662628212.png
 
Upvote 0

Forum statistics

Threads
1,226,833
Messages
6,193,212
Members
453,780
Latest member
Nguyentam2007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top