Change language

The VSTACK Function | Dynamic Arrays | Stacking variable data into a single array

The VSTACK Function | Dynamic Arrays | Stacking variable data into a single array

- Were gonna dive straight into this.

Were going to do...

VSTACK is one of the new 14 functions that came out a few weeks ago in Excel.

And somebody emailed me a question and I started immediately thinking VSTACK.

Now, initially I did think Power Query and I could kind of see a strategy in Power Query.

But then when I started to work with it, I saw Im gonna need a whole lot of queries.

Im gonna have to pull data apart, put it back together.

And it would work.

But then, yeah, VSTACK.

I started thinking, if I can get the pieces to this solution and then do a VSTACK at the end, beautiful.

So, look all the way toward the end and then start backing up.

[upbeat music] This person sent me Round 1, Round 2, Round 3 as examples.

But the easy one to start with is Round 2.

We have all of these teams from A to Q and the goal is to get the top 10.

Okay, weve got teams F through I, F scored 31 points, I 22, all of those in between.

Great, the top 10.

But now, it gets odd when we have to deal with ties.

In Round 1, there are nine teams from F down to H and then the statement saying, four teams with 21.

And one more layer to this.

In Round 3, there are six teams showing.

And then the statement, five teams with 25.

Thats the goal here.

And another thing, if its not clear, there are only 10 rows.

We cant have an 11th row or whatever, but we can have it shorter.

Let me show you what I built and then Im gonna show you how I built it.

Here we go, weve got all of these teams.

The Antelopes are at the top, Neon Rascals are in the 10th position, as you can see by that arrow, at 36.

Now lets do this, and Im gonna show you that this doesnt have to be in order the way that Ive made it.

Lets say that weve got 36 for the Bears.

Now that statement flipped.

Now it says, two teams tied at 36.

Two teams tied at 10th, right? Two teams tied at 10th, like Tommy Tatimus took two Ts and tied em up top two tall trees.

Yeah, can you say that? And how about Betty Bodder bought some bitter butter, said, "If I put it in my batter, itll make my batter bitter." So Betty Bodder bought some better butter, put it in her batter and it made her batter better.

Okay, lets get back to Excel.

Lets make another team at 37. Put that there.

Okay, look at that.

Weve got the top four and then six teams tied at 37.

Lets sort this, Data, and then sort descending.

And there we go.

The Tarpon are at 10th place with 37.

And yes, we do have six teams tied at 37.

Are we ready to go into how this was built? Im gonna delete this and show you how its done.

Take a deep breath.

Lets do it.

First, we need the score thats in 10th place.

Go here, 10th Largest.

Equals LARGE.

Open parentheses, go over here.

What is the 10th largest score right there, comma, and then 10, Enter.

31.

Now Im gonna put a header here.

Count ties.

Okay, thats later. Make these bold.

Now I want to get the titles, the column headers in anticipation of doing the VSTACK to wrap this up.

Equals the TEAM and the SCORE, okay.

You can see the blue line. That is a Dynamic Array.

Now lets slide over.

Now lets get all teams that are tied at that 10th score.

Equals FILTER, open parentheses.

Filter what? Were gonna filter this whole column. This whole table.

Okay.

Comma.

Whats the filter criteria where the score column is equal to that 10th number? Got it.

So there are two teams tied for 10th, the Rustic Vision and the Wizards.

We want to count the ties.

Equals COUNTA, open parentheses, go over here.

And we need to hashtag to count the whole array.

Hashtag, Enter.

And then this needs to be divided by two because there are two columns.

Divided by two, Enter.

Now I need to grab the teams that have scores that are greater than that 10th score.

Greater than, not greater than or equal to.

We go here to K11.

All right, equals FILTER, open parentheses.

Filter what? Filter this table.

Comma.

For what criteria? Where this column, the scores are greater than the value here.

Enter.

Now we need to get that statement.

Equals, grab this two.

Okay? And, double quote, space, Teams Tied at, colon, double quote.

Theres the statement.

Then we need... Its 31.

Equals, right here. Good.

Now weve got all of the pieces.

Now lets stack this up.

Im gonna go here, above, because I want to get the headers over in L and M.

Equals IF, open parentheses. Okay? Because we gotta account for if there are no ties and we can grab all 10, versus if there are ties.

IF this number is equal to one then VSTACK.

Double click there.

Open parentheses.

We need the headers.

Right there, L1, hashtag, comma.

Now we can filter.

FILTER, open parentheses.

Filter what? Filter this array.

That table, actually.

Comma, to include the score is greater than or equal to the value here.

Close the FILTER, close the VSTACK.

Comma, if false, VSTACK.

We got the open parenthesis, we wanna stack those headers.

Comma. Whats next that we wanna stack? We wanna stack the data here, okay? And we need the hashtag to get that whole array.

Comma. Now we want this right here.

Close the VSTACK, close the if statement, Enter.

Ah! Got it.

Got it. Got it.

Now lets change some stuff.

Lets say that the Antelopes were 40, okay? And then, the Winter were 39, okay? Two teams tied at 36.

Lets add...

The New Raisins also had 36.

And lets say the Vipers were also 36.

The Tarpon were 36.

The Monarchs were also 36.

Less sort this so you can see.

Look at this.

Six teams in the top six positions, six unique teams.

And six teams tie at 10th score.

And that, my friends, is how VSTACK can help us out.

And it was so much easier than Power Query.

See you next time.

[upbeat music]

Shop

Learn programming in R: courses

$

Best Python online courses for 2022

$

Best laptop for Fortnite

$

Best laptop for Excel

$

Best laptop for Solidworks

$

Best laptop for Roblox

$

Best computer for crypto mining

$

Best laptop for Sims 4

$

Latest questions

NUMPYNUMPY

Common xlabel/ylabel for matplotlib subplots

12 answers

NUMPYNUMPY

How to specify multiple return types using type-hints

12 answers

NUMPYNUMPY

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

12 answers

NUMPYNUMPY

Flake8: Ignore specific warning for entire file

12 answers

NUMPYNUMPY

glob exclude pattern

12 answers

NUMPYNUMPY

How to avoid HTTP error 429 (Too Many Requests) python

12 answers

NUMPYNUMPY

Python CSV error: line contains NULL byte

12 answers

NUMPYNUMPY

csv.Error: iterator should return strings, not bytes

12 answers

News


Wiki

Python | How to copy data from one Excel sheet to another

Common xlabel/ylabel for matplotlib subplots

Check if one list is a subset of another in Python

sin

How to specify multiple return types using type-hints

exp

Printing words vertically in Python

exp

Python Extract words from a given string

Cyclic redundancy check in Python

Finding mean, median, mode in Python without libraries

cos

Python add suffix / add prefix to strings in a list

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

Python - Move item to the end of the list

Python - Print list vertically