HOW TO LINK (CONCATENATE) TWO ARRAYS IN ONE ARRAY FORMULA?

ruiseixas

New Member
Joined
Jul 3, 2005
Messages
6
Hi,

I am having difficulty on this one: :(

Suppose you have these 2 arrays, each one from one sheet:
{1, 3, 5} and {2, 4, 6}

and from the both arrays I want to extract the 1st or 2nd lowest elements into two different cells.

Like this:
Cell1 = 1
Cell2 = 2

Nothing complicated if some how we link the two arrays in to this one:
{1, 3, 5, 2, 4, 6}

Solution:
Cell1 -> {=SMALL ({1, 3, 5, 2, 4, 6}, 1)
Cell2 -> {=SMALL ({1, 3, 5, 2, 4, 6}, 2)

The plus sign (+) don't work and gives {3, 7, 11}, and that is not what I need.

Any one out there can figure out a way (array formulas) to make the following without macros?

LINK ({1, 3, 5}, {2, 4, 6}) = {1, 3, 5, 2, 4, 6}

Thanks folks.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
If both arrays are on the same sheet you can define a Named Range that includes both arrays: Insert | Name | Define - then ctrl-click to define as many ranges as you need. Alternatively, you can define the Named Range in VBA, or as a union of one or more dynamic ranges - but I think both of these are probably more than you need.

The Named Range can be used in the Small function, as:
Code:
=small(DataRange, x)
where x is any integer
 
Upvote 0
Hi ruiseixas
Welcome to the board

Suppose you have these 2 arrays, each one from one sheet .... and from the both arrays I want to extract the 1st or 2nd lowest elements into two different cells.

You can do it with just native excel functions using an auxilliary array to filter the elements from the 2 ranges.

Example: You want the second smallest value in ranges Sheet1!A1:A4 and Sheet2!A1:A3

In this case you use an auxilliary array of 7 elements to build dinamically a concatenated array of the 2 ranges, for the first 4 elements you choose the first range and for the other 3 the second range.

Code:
=SMALL(IF(ROW(1:7)<=4,N(OFFSET(Sheet1!$A$1:$A$4,-1+ROW(1:7),0)),N(OFFSET(Sheet2!$A$1:$A$3,-1+ROW(1:7)-4,0))),2)

Of course it is not very practical to determine in each case those hardcoded numbers and ranges.

A more elegant and general solution is

Define both ranges as named ranges Rng1 refers to Sheet1!A1:A4 and Rng2 refers to Sheet2!A1:A3

And now you can use the general formula:
Code:
=SMALL(IF(ROW(INDIRECT("1:"&ROWS(Rng1)+ROWS(Rng2)))<=ROWS(Rng1),N(OFFSET(Rng1,-1+ROW(INDIRECT("1:"&ROWS(Rng1)+ROWS(Rng2))),0)),N(OFFSET(Rng2,-1+ROW(INDIRECT("1:"&ROWS(Rng1)+ROWS(Rng2)))-ROWS(Rng1),0))),2)

In this formula you dont have to worry about the sizes of the ranges.

Hope this helps
PGC
 
Upvote 0
EASY SOLUTION

Hi guys,

after some thinking, I solve the problem this way:

we have these two arrays each one in one distinct sheet

Code:
Sheet1!A1:A3 = {1, 3, 5}
Sheet2!A1:A3 = {2, 4, 6}

and we want to get the 2nd element of the total amount of data using the SMALL function, so we do the following

Code:
{=SMALL ({1, 0}*Sheet1!A1:A3 + {0, 1}*Sheet2!A1:A3, 2}

Attention must be taken to the fact that the two or more arrays must be of the same size, in this particular case size 3.

Thanks again.
 
Upvote 0
Re: EASY SOLUTION

Hi guys,

after some thinking, I solve the problem this way:

we have these two arrays each one in one distinct sheet

Code:
Sheet1!A1:A3 = {1, 3, 5}
Sheet2!A1:A3 = {2, 4, 6}

and we want to get the 2nd element of the total amount of data using the SMALL function, so we do the following

Code:
{=SMALL ({1, 0}*Sheet1!A1:A3 + {0, 1}*Sheet2!A1:A3, 2}

Attention must be taken to the fact that the two or more arrays must be of the same size, in this particular case size 3.

Thanks again.

=SMALL(Sheet1:Sheet2!A1:A3,2)

would suffice.
 
Upvote 0
NOT REALLY

Hi guys,

after some thinking, I solve the problem this way:

we have these two arrays each one in one distinct sheet

Code:
Sheet1!A1:A3 = {1, 3, 5}
Sheet2!A1:A3 = {2, 4, 6}

and we want to get the 2nd element of the total amount of data using the SMALL function, so we do the following

Code:
{=SMALL ({1, 0}*Sheet1!A1:A3 + {0, 1}*Sheet2!A1:A3, 2}

Attention must be taken to the fact that the two or more arrays must be of the same size, in this particular case size 3.

Thanks again.

=SMALL(Sheet1:Sheet2!A1:A3,2)

would suffice.

It works, but only on the same cells for all sheets, and with the use of array formulas totally limited.
The formula that I write before its incorrect, I will give the correct one for the case of lines and rows:

LINES:
Code:
{=SMALL ({1, 0}*Sheet1!A1:C1 + {0, 1}*Sheet2!A1:C1, 2)}

ROWS:
Code:
{=SMALL (Sheet1!A1:A3*{1\ 0} + Sheet2!A1:A3*{0\ 1}, 2)}
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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