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

[dave2018]

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

 

[dave2018]

bump

 

[Rick Rothstein]

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)?

 

[dave2018]

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

 

[dave2018]

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)...

 

[Marcelo Branco]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Array1​

[/TD]
[TD][/TD]
[TD]

Array Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Array2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[dave2018]

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...)

 

[Marcelo Branco]

Ok, this is simpler.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Array1​

[/TD]
[TD]

Array2​

[/TD]
[TD][/TD]
[TD]

Array Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

8​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

9​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

12​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[dave2018]

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 )

 

[Marcelo Branco]

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.