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]

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.

sorry for not being so clear... This is the best example I can give. I'm asking for this array for many different uses... and i have been working on this for hours, and i can't wrap my head around it

 

[Marcelo Branco]

sorry for not being so clear... This is the best example I can give. I'm asking for this array for many different uses... and i have been working on this for hours, and i can't wrap my head around it

Different uses? Sorry, if you cannot show us some examples i don't know how to help.

M.

 

[dave2018]

Excel 2016 (Windows) 32 bit
	A	B
1	Product ID	Price
2	1	$20.00
3	2	$35.00
4	3	$44.00
5	4	$43.00
6	5	$16.00
7	6	$40.00
8	7	$12.00
9	8	$45.00
10	9	$25.00
11	10	$25.00
12	11	$16.00
13	12	$14.00
14	13	$11.00
15	14	$43.00
16	15	$33.00
17	16	$45.00
Products

​

Excel 2016 (Windows) 32 bit
	A	B
1	Product ID	Price
2	17	$59.00
3	18	$53.00
4	19	$58.00
5	20	$45.00
6	21	$41.00
7	22	$36.00
8	23	$49.00
9	24	$48.00
10	25	$58.00
11	26	$37.00
12	27	$41.00
13	28	$53.00
14	29	$33.00
15	30	$54.00
16	31	$64.00
17	32	$34.00
Products 2

I need a formula that says =Large({pricecolumn1;pricecolumn2},3) Is that good enough of an example?

 

[dave2018]

I found this interesting post:

Hi gvanbee:

While holding the CTRL key
Highlite A1:A15, C1:C12, D1:D5
Then INSERT|NAME|DEFINE -- CombinedRange -- OK

The CombinedRange would then consist of:
=Sheet9!$A$1:$A$15,Sheet9!$C$1:$C$12,Sheet9!$D$1:$D$5

Mine is Sheet9 -- please adjust your Sheet name to suit.
Please post back if it works for you -- otherwise explain a little further and let us take it from there.

Regards!

Yogi


[ This Message was edited by: Yogi Anand on 2002-10-02 23:09 ]

In it he explains that when creating a named range, you can say array1,array2,array3 and it joins them.

is there a way to do that in a single formula (without named ranges...)

that is my question!

 

[Marcelo Branco]

It would require a very complex formula.
For example, a formula related to post 13 - to get the 3rd largest - would be:
=LARGE(IFERROR(INDEX(Products!B$2:B$17,N(IF(1,ROW(A$1:A$32)-ROW(A$1)+1))),INDEX('Products 2'!B$2:B$17,N(IF(1,ROW(A$1:A$32)-ROW(A$1)+1))-ROWS(Products!B$2:B$17))),3)
Ctrl+Shift+Enter

With 3 or more range the complexity would increase, making the formula not a practical solution. I think you should think about a VBA solution.

M.

 

[dave2018]

Thanks Marcelo. I don't want a vba solution... But i will go for another round to figure it out .

Thanks for your help.

 

[dave2018]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	1		9		1,2,3,4,5,6,7,8,9,10
2	2		10
3	3
4	4
5	5
6	6
7	7
8	8
Sheet1

Cell Formulas
Range	Formula
E1	{=TEXTJOIN(",",0,INDEX(CHOOSE(N(IF(1,{1,2})),A1:A8,C1:C2),N(IF(1,IF(N(IF(1,ROW(INDIRECT("A1:A"&COUNTA(A1:A8,C1:C2)))))>COUNTA(A1:A8),N(IF(1,ROW(INDIRECT("A1:A"&COUNTA(A1:A8,C1:C2)))))-COUNTA(A1:A8),N(IF(1,ROW(INDIRECT("A1:A"&COUNTA(A1:A8,C1:C2)))))))),N(IF(1,IF(N(IF(1,ROW(INDIRECT("A1:A"&COUNTA(A1:A8,C1:C2)))))>COUNTA(A1:A8),N(IF(1,ROW(INDIRECT("A1:A"&COUNTA(A1:A8,C1:C2)))^0+1)),N(IF(1,ROW(INDIRECT("A1:A"&COUNTA(A1:A8,C1:C2)))))^0)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I think this is towards what I'm looking for. I put it into the textjoin just to visualize. makes any sense?

 

[Rick Rothstein]

Thanks Marcelo. I don't want a vba solution...

If you should change your mind, this function will accept multiple arrays which can consist of VBA arrays as well as Excel ranges (simply pass them in with a comma delimiter between them)...

[table="width: 500"]
[tr]
	[td]Function MakeArray(ParamArray Arr() As Variant) As Variant
  Dim X As Long, Elements As Long, A As Variant, V As Variant, Vals As Variant, Result As Variant
  For Each A In Arr
    Vals = A
    For Each V In Vals
      Elements = Elements + 1
    Next
  Next
  ReDim Result(1 To Elements)
  For Each A In Arr
    For Each V In A
      X = X + 1
      Result(X) = V
    Next
  Next
  MakeArray = Result
End Function[/td]
[/tr]
[/table]

 

[Marcelo Branco]

If you should change your mind, this function will accept multiple arrays which can consist of VBA arrays as well as Excel ranges (simply pass them in with a comma delimiter between them)...

With regard to post 17:
Using Rick's function all you need is
E1
=TEXTJOIN(",",1,MakeArray(A1:A8,C1:C2))

M.

 

[dave2018]

yes. i will keep that code for refrence, but i really try to stay away from udf when possible. just my 'ocd'. thanks though for all your help .

i will still try to develop my formula above.