Append cells or values to an existing array of cells in formula

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
223
Hi,

I have an array formula that ends off with {"1";"2";"3","4"}

I need a formula trick that can add cells or text to this column, so that when i evaluate it, it will become {"1";"2";"3","4","5","6","7"}

The latter numbers will be coming from cells or hard-coded values. I need both ways (which shouldn't make a difference anyways).

I've tried the choose({1;2},{array1},{array2}) method, but it's not working like i need it. it need to be robust to expand to any size i feed it.

(probably will need indirect in the beginning)

Hope i'm clear enough!

Thanks
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I need a formula trick that can add cells or text to this column, so that when i evaluate it, it will become {"1";"2";"3","4","5","6","7"}
I am not sure there is a non-VBA solution to what you want, but I wanted to double-check... did you really mean to mix different delimiters in your array (semi-colons for rows, commas for columns)?
 
Upvote 0
it's for SURE not a vba. it have a feeling like it's so logical! I think i just need the right arrangement of an array (constant) created by indirect or something like that.

Thanks
 
Upvote 0
This is the closest i've got. this takes A1:B4 and makes it {A1;A2;A3;A4;B1;B2;B3;B4}

=INDEX(A1:B4,N(IF(1,{1;2;3;4;1;2;3;4})),N(IF(1,{1;1;1;1;2;2;2;2})))

But i need it to take 2 SEPARATE arrays (unlike this where they are touching and are refrenced in 1 refrence)...
 
Upvote 0
Not sure i understand what you want

Say you want an array with 7 elements that contains the 4 smallest values in A2:A11 and the values in A14:A16
Something like this

A
B
C
1
Array1​
Array Result​
2
1​
1​
3
8​
2​
4
7​
3​
5
4​
4​
6
6​
5​
7
3​
6​
8
9​
7​
9
10​
10
2​
11
12​
12
13
Array2​
14
5​
15
6​
16
7​

<tbody>
</tbody>


Select C2:C8 (blue area) and in the formula bar paste this formula
=IF(ROW(C2:C8)-ROW(C2)+1<=4,SMALL(A2:A11,ROW(C2:C8)-ROW(C2)+1),INDEX(A14:A16,N(IF(1,ROW(C2:C8)-ROW(C2)+1-4))))
Ctrl+Shift+Enter

Hope this helps

M.
 
Last edited:
Upvote 0
Not exactly, I want array 1 and then array 2 on bottom of that. (not nessasarily in separate cells), rather i want it in 1 cell, so i can do further calculations on it...

so in your example, i want a formula to spit out an array that looks like this ={1;8;7;4;6;3;9;10;2;12;5;6;7}

Thanks

edit: keep in mind that the 2 arrays may be more than 2, and that they might be on separate pages... (so u cant just refrence column A:A and filter out the garbage...)
 
Last edited:
Upvote 0
Ok, this is simpler.


A
B
C
D
1
Array1​
Array2​
Array Result​
2
1​
5​
1​
3
8​
6​
8​
4
7​
7​
7​
5
4​
4​
6
6​
6​
7
3​
3​
8
9​
9​
9
10​
10​
10
2​
2​
11
12​
12​
12
5​
13
6​
14
7​
15

<tbody>
</tbody>


Select D2:D14 and in the formula bar paste...
=IFERROR(INDEX(A2:A11,ROW(D2:D14)-ROW(D2)+1),INDEX(B2:B4,ROW(A2:A14)-ROW(A2)+1-ROWS(A2:A11)))
Ctrl+Shift+Enter

M.
 
Upvote 0
Thanks, but i was really asking about putting all that into an array that i can use in a single cell, so i can do more calculations with that. (for example: use textjoin to separate the final list in 1 cell, bu that is not what i need it for...) I have been trying for ages... :)

Thanks for your patience (or maybe u dont have ;))
 
Upvote 0
Please, show us your real scenario and what exactly you are trying to do. Otherwise we will be wasting time suggesting formulas that do not exactly meet the requirements. Show us a real example along with the desired results.

M.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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