Combining 3 Arrays

  • Thread starter Thread starter David W
  • Start date Start date
D

David W

Good Morning All,
Got one that I cannot figure out yet!
I have got 3 different arrays, I need to combine them together. They are on
3 different sheets. How would you combine them if the index changes on one
of them
example
Column A is the index

sheet1
A B C D E
1) 1 3 8
2) 2 9 6 2
3) 4 8 2 1

sheet2
A B C D E
1) 1 2 1
2) 2 1 8 3
3) 3 2 6 3

sheet3
A B C D E
1) 1 1
2) 2 3 1 6
3) 3 4 2 1

Results would be sheet4
A B C D E
1) 1 6 9
2) 2 13 15 11
3) 3 6 8 3 1
4) 4 8 2 1

I just bumfuzzled with this one
 
When I put the formula in A1 on sheet4 I get an error that wont explain why.
More than likely I used it incorrectly. What I was needing clarifacation on
is how to use it.
There are 160 possible rows and 16 columns on each sheet that it needs to
consolidate. I can fiqure out how to manipulate the table, but, is this
pasted in each cell or does it function like an array. This is kinda complex
and I do appreciate your time. Can you help me again
 
Insert the formula in B2 of Sheet4, then fill over to E5, then fill down
to B5:E5. But as I previously posted, it gives the wrong result in A4:D4.

Alan Beban
 
I have got 3 different arrays, I need to combine them together. They are on
3 different sheets. How would you combine them if the index changes on one
of them ...
Column A is the index

sheet1
A B C D E
1) 1 3 8
2) 2 9 6 2
3) 4 8 2 1
Click to expand...

I'll name this, Sheet1!A1:E3, Array1.
sheet2
A B C D E
1) 1 2 1
2) 2 1 8 3
3) 3 2 6 3
Click to expand...

I'll name this, Sheet2!A1:E3, Array2.
sheet3
A B C D E
1) 1 1
2) 2 3 1 6
3) 3 4 2 1
Click to expand...

I'll name this, Sheet3!A1:E3, Array3.
Results would be sheet4
A B C D E
1) 1 6 9
2) 2 13 15 11
3) 3 6 8 3 1
4) 4 8 2 1
Click to expand...

So the figures in columns B to E are sums of the corresponding columns and row
IDs in column A from the other worksheets?

The expedient way to do this involves pulling the row IDs for column A first. In
Sheet4!A1 and Sheet4!A1 enter the formulas

A1:
=MIN(INDEX(Array1,0,1),INDEX(Array2,0,1),INDEX(Array3,0,1))

A2:
=MIN(IF(INDEX(Array1,0,1)>MAX(A$1:A1),INDEX(Array1,0,1)),
IF(INDEX(Array2,0,1)>MAX(A$1:A1),INDEX(Array2,0,1)),
IF(INDEX(Array3,0,1)>MAX(A$1:A1),INDEX(Array3,0,1)))

Fill A2 down as far as needed. The filled formula will evaluate to 0 when there
are no more distinct column A values in the source arrays. Delete the cells
evaluating to 0.

Then enter this formula in Sheet4!B1.

B1:
=SUMIF(INDEX(Array1,0,1),$A1,INDEX(Array1,0,COLUMN()))
+SUMIF(INDEX(Array2,0,1),$A1,INDEX(Array2,0,COLUMN()))
+SUMIF(INDEX(Array3,0,1),$A1,INDEX(Array3,0,COLUMN()))

Fill B1 right into C1:E1, then select B1:E1 and fill down as far as needed
(mathich the rows in which there are ID numbers in column A).
 
Harlan

I cant get this to work on a defined array, another words on each sheet I
have got a 16 column and 160 rows in a setup form, to the right of that I
have got an array that looks at the 16 columns and 160 rows and it puts them
in order. (In column A I have got the numbers 1 through 160 not all of the
time will each number be used ,so I am using the array to sort from small to
large then return the data to the right of it) That array starts in BD1 and
goes to BW160 on all 3 sheets. I put your caculation in as told and I still
get an error Here is what I put in a1

=MIN(INDEX(Jan!BD1:BD160,0,1),INDEX(Feb!BD1:BD160,0,1),INDEX(March!BD1:BD160
,0,1))
I get a #num! error

in a2 I put the following

=MIN(IF(INDEX(Jan!bd1:bd160,0,1)>MAX(A$1:A1),INDEX(Jan!bd1:bd160,0,1)),
IF(INDEX(Feb!bd1:bd$160,0,1)>MAX(A$1:A1),INDEX(Feb!bd1:bd160,0,1)),
IF(INDEX(March!bd1:bd160,0,1)>MAX(A$1:A1),INDEX(March!bd1:bd160,0,1)))
I get a value error

wil this work with a defined array, here is the array that I am using on the
3 sheet to do as I said above
{=INDEX(A$1:A$170,SMALL(IF($X$10:$X$170,ROW($X$10:$X$170),""),ROW()))}
 
David W said:
I cant get this to work on a defined array, another words on each sheet I
have got a 16 column and 160 rows in a setup form, to the right of that I
have got an array that looks at the 16 columns and 160 rows and it puts them
in order. (In column A I have got the numbers 1 through 160 not all of the
time will each number be used ,so I am using the array to sort from small to
large then return the data to the right of it) That array starts in BD1 and
goes to BW160 on all 3 sheets. I put your caculation in as told and I still
get an error Here is what I put in a1

=MIN(INDEX(Jan!BD1:BD160,0,1),INDEX(Feb!BD1:BD160,0,1),
INDEX(March!BD1:BD160,0,1))
I get a #num! error
Click to expand...

The only way this formula evaluates to #NUM! is if there's a cell in once of
these three ranges that evaluates to #NUM!
in a2 I put the following ....
I get a value error
Click to expand...

If A1 evaluates to an error, A2 and subsequent will as well.
 
Back
Top