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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

ruiseixas

New Member
Joined
Jul 3, 2005
Messages
6

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

ruiseixas

New Member
Joined
Jul 3, 2005
Messages
6
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)}
 

Forum statistics

Threads
1,136,607
Messages
5,676,778
Members
419,651
Latest member
alexanderguhr

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
Top