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

D

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
 
Sort by date Sort by votes
Marcelo Branco said:
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.
Click to expand...

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 :(
 
Upvote 0

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).
dave2018 said:
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 :(
Click to expand...

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

M.
 
Upvote 0

Excel 2016 (Windows) 32 bit
AB
1Product IDPrice
21$20.00
32$35.00
43$44.00
54$43.00
65$16.00
76$40.00
87$12.00
98$45.00
109$25.00
1110$25.00
1211$16.00
1312$14.00
1413$11.00
1514$43.00
1615$33.00
1716$45.00
Products

​


Excel 2016 (Windows) 32 bit
AB
1Product IDPrice
217$59.00
318$53.00
419$58.00
520$45.00
621$41.00
722$36.00
823$49.00
924$48.00
1025$58.00
1126$37.00
1227$41.00
1328$53.00
1429$33.00
1530$54.00
1631$64.00
1732$34.00
Products 2


I need a formula that says =Large({pricecolumn1;pricecolumn2},3) Is that good enough of an example?
 
Last edited:
Upvote 0
I found this interesting post:

Yogi Anand said:
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 ]
Click to expand...

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!
 
Upvote 0
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.
 
Upvote 0
Thanks Marcelo. I don't want a vba solution... But i will go for another round to figure it out :).

Thanks for your help.
 
Upvote 0

Excel 2016 (Windows) 32 bit
ABCDE
1191,2,3,4,5,6,7,8,9,10
2210
33
44
55
66
77
88
Sheet1
Cell Formulas
RangeFormula
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?
 
Last edited:
Upvote 0
dave2018 said:
Thanks Marcelo. I don't want a vba solution...
Click to expand...
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)...
Code: 
[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]
 
Upvote 0
Rick Rothstein said:
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)...
Click to expand...


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

M.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Append Cell Data with .jpg
2
Replies
10
Views
129
jolivanes
jolivanes
N
  • Question Question
How to split cell value into multiple bands using array formula?
2
Replies
10
Views
367
RoryA
RoryA
R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
78
Alex Blakenburg
A
J
  • Question Question
formula to match cell on current worksheet to a cell in an array on a different worksheet, and then display value of cell in same row of array
Replies
2
Views
244
Jmoz092
J
2
  • Question Question
Use cell value in formula
Replies
3
Views
148
Fluff
Fluff

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

Share this page

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