LET me add value's to an array

Dear all

LET me explain what I want. It's specific; I got in my LET function two array's results from a Filter in the same Let function and a variable from a Cell.

Please Login or Register  to view this content.

Expected result : array_Final contains {1;5;3;4;2;7}

Three questions:

How can I add/merge two array's?
How can I add a variable to the end of an array?
Is there a way to insert a variable at a certain array location?


Thank you very much

Did you look at VSTACK?

e.g. =VSTACK(array_1;array_2)

You could add SORT to this if you wanted to.

Not quite sure what you are expecting for the final array, sorry.

Oh, well! If you can't even be bothered to try it ...

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C
1	1		=LET( array_1, {1;5;3}, array_2,{4;2}, myVar,A1, array_merged, VSTACK(array_1,array_2), array_merged )
2	5
3	3
4	4
5	2

Sheet: Sheet1

And with everything included:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C
1	1	7	=LET( array_1, {1;5;3}, array_2,{4;2}, myVar,B1, array_merged, SORT(VSTACK(array_1,array_2,myVar),,1), array_merged )
2	2
3	3
4	4
5	5
6	7

Sheet: Sheet1

Or, if you want it horizontally:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F
14	=LET( array_1, {1,5,3}, array_2,{4,2}, myVar,A16, array_merged, SORT(HSTACK(array_1,array_2,myVar),,1), array_merged )
15	1	5	3	4	2	7
16	7

Sheet: Sheet1

Correction to the SORT element of the HSTACK version:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F
14	=LET( array_1, {1,5,3}, array_2,{4,2}, myVar,A16, array_merged, SORT(HSTACK(array_1,array_2,myVar),,1,TRUE), array_merged )
15	1	2	3	4	5	7
16	7

Sheet: Sheet1

Any good?

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

If I copy/past your sample I get also #Name error.

Yes, because you are using a European locale, which is why I supplied you with a workbook.

This machine has version 16, but not >16.65, the first with the Stacks

They are new in MS365. I assumed that you were using a newer version as you are using the LET function.

How does the LET result knows the difference between VStack and HStack? Has finalArray or is an Array a hidden structure even it is just a variable with inherited knowledge to print it in a certain direction or rectangular range?

VSTACK means vertical stack, HSTACK means horizontal stack.

I think about to many assumptions.

Only that you were using MS365 because of LET. LET was new in Excel 2021.

Your not having MS365 isn't a flaw, it just means that VSTACK and HSTACK won't work for you.

BTW the docs show's it is used for cells, I did look at the samples before.

Online tutorials rarely cover every use of a new function - it's ALWAYS worth trying something out.

Unfortunately, I don't know how to do it without those newer functions. Sorry.

Actually, If I did try Stacks; I did get the #Name error;

In post #3 in response to my question about whether you'd tried VSTACK, you said this:

Actually no,

I'm not clear if you want to emulate VSTACK or HSTACK. This will do the former:

Formula:

Please Login or Register  to view this content.

... and this does the latter:

Formula:

Please Login or Register  to view this content.

Was any/some/all of that directed at me?

Was any/some/all of that directed at me?

Originally Posted by Glenn Kennedy

Was any/some/all of that directed at me?

No why? I just finished checking your code.


A Side Note; no need to emulate a V or HStack. Just adding two local LET array's in an LET local variable.

Thanks for asking.

I did not understand what you wanted. Please post ab Excel file showing before/after.

It's all aimed at me, Glenn.

@lord anubis - thanks for sharing your set-up with me.

In terms of HStack, I don't understand at all why you think it is a misnomer: VStack returns a vertical range (single column one cell wide) and HStack returns a horizontal range (single row one cell deep).

Does Glenn's suggestion help you at all?

Originally Posted by AliGW

It's all aimed at me, Glenn.
VStack returns a vertical range (single column one cell wide) and HStack returns a horizontal range (single row one cell deep).

If you check your own sample then you will see HStack result is not on one single row only.

I did wrote that before; did you find that? Also do you know more about my secondary question about the knowledge of an array about how to print itself.

Never mind, another difference between versions. Did just update my other machine to the latest version 16.73 from a few days back and it looks better now. But again, the target is without using Stacks.

Just to clarify, did my VSTACK version produce the correct result? If so, Glenn can use that as a guide.

HStack result is not on one single row only.

Oh, yes it is!!! The cell on the row below is the MyVAR reference cell.

Here it is without the reference cell:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F
14	=LET( array_1, {1,5,3}, array_2,{4,2}, myVar,7, array_merged, SORT(HSTACK(array_1,array_2,myVar),,1,TRUE), array_merged )
15	1	2	3	4	5	7
16
17

Sheet: Sheet1

Also do you know more about my secondary question about the knowledge of an array about how to print itself.

On a UK locale, a comma will produce a horizontal array and a semi-colon a vertical one:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	Q	R	S	T	U	V
3	=LET( array_1, {1;5;3}, array_1)			=LET( array_1, {1,5,3}, array_1)
4	1			1	5	3
5	5
6	3

Sheet: Sheet1

I see you have edited your previous post to say that you have updated your version now, so how about this?

It uses TEXTJOIN and TEXTSPLIT.

Please Login or Register  to view this content.

This is the same as Post 11, except I have used two single column arrays. I merged them and then used the merged set in a final calculation to return only the odd numbers. If this is not what you wanted, then I have totally misundersttood you.

And this will give a horizontal array:

Please Login or Register  to view this content.

Even more differences.

Ali, I think TEXTSPLIT was released at the same time as VSTACK, so it probably won't be available to the OP. either.

Originally Posted by lord anubis

Even more differences.

To whom are you talking? What differences?

The OP has just said that he has now updated his version. See the late edit he made to his previous post. I take this to mean that we can now use 365 functions.

Originally Posted by AliGW

The OP has just said that he has now updated his version. See the late edit he made to his previous post. I take this to mean that we can now use 365 functions.

No, the goal is the older version, please please stay at my question. No Stacks. the target machine has no stacks.

Do you read "But again, the target is without using Stacks."


I will check your new sample with TEXTSPLIT; As far as I know those are both available at the target machine.
Thanks

Even more differences.

Of your making!

When you define your arrays, they must ALL be separated either with commas or semi-colons, NOT a mix of the two.

=LET(
array_1; {1;5;3},
array_2; {4;2},
...)

OR

=LET(
array_1; {1,5,3},
array_2, {4,2},
...)

No, the goal is the older version, please please stay at my question.

The latest formula does not use either VSTACK or HSTACK. What do you mean exactly by stacks?

I am trying VERY HARD to help you here, but you really are not making it easy. You won't show us what you want, so we're having to guess. Hardly surprising, then, that we are not getting what you want.

You are not making yourself clear at all.

Do you read "But again, the target is without using Stacks."

YES, I am reading EVERYTHING, but you keep editing your posts and you are not answering our questions clearly.

Show us in an Excel file what you would expect your formula to produce. Do this manually.

It's because you have a European locale (as I have mentioned).

I found this online:

Countries using a decimal comma however use a \ as a column delimiter and a ; for rows respectively.

Change the commas EITHER to semi-colons OR a slash \.

Let me know if that works.

I think I finally understand what's wanted!!

If the OP was able to use HSTACK or TEXTSPLIT your approach Ali looks correct. I could replicate it using FILTERXML... but it doesn't work on a Mac OS.

Well done, Glenn! I am just trying to get the OP to tweak the formula for his European locale. Once this is done, I think we'll be there.

I shall be very glad when this is over ...

So, to summarise, arrays need to look like this:

=LET(
array_1; {1;5;3},
array_2; {4;2},
...)

or this:

=LET(
array_1; {1\5\3},
array_2, {4\2},
...)

Try this (no VSTACK, no TEXTJOIN). It may need ; or \ as array separators.

Nice one, Glenn. At least I got the heavy lifting done!!!

Edit: it needs a tweak to return real numbers:

Formula:

Please Login or Register  to view this content.

And it can, of course, be shortened to this:

Please Login or Register  to view this content.

Lord A...

what happens when you paste this into your Excel:

Formula:

Please Login or Register  to view this content.

Originally Posted by Glenn Kennedy

Lord A...

what happens when you paste this into your Excel:

Formula:

Please Login or Register  to view this content.

It works.......so far.

The difference between ';' and '\' is none; instead of the written difference between ';' for Vertical and ',' ( \) for Horizontal.
So I did Transpose the result to get a Row.

Please Login or Register  to view this content.

Thank you both. No V or HStack needed.


What I do understand now is that a Array is a kind of part of a matrix or at least a substitute, and it, the Array is or a row or a column from the matrix. And so will it be used in calculation and visibility ( read showing up in a row or column ). Still the question... never mind.


BTW, I do have this saved at my other computer; but was out of my reach.

Please Login or Register  to view this content.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Glad we got there in the end.