joining ranges

[DavidObeid]

If I have 3 worksheets (sheet1, sheet2, sheet3) each with a range o
cells (1 column wide and varying rows long) named "range1", "range2
and "range3" respectively, can I write a formula to report the content
of all 3 ranges into column 1 of sheet4?

Thanks in advance,

Dav

 

[Harlan Grove]

If I have 3 worksheets (sheet1, sheet2, sheet3) each with a range of
cells (1 column wide and varying rows long) named "range1", "range2"
and "range3" respectively, can I write a formula to report the contents
of all 3 ranges into column 1 of sheet4?

Sheet4!A1:
=INDEX(Range1,1)

Sheet4!A2:
=IF(COUNTA(A$1:A1)<ROWS(Range1),OFFSET(Range1,COUNTA(A$1,A1),0,1,1),
IF(COUNTA(A$1:A1)-ROWS(Range1)<ROWS(Range2),
OFFSET(Range2,COUNTA(A$1:A1)-ROWS(Range1),0,1,1),
IF(COUNTA(A$1:A1)-ROWS(Range1)-ROWS(Range2)<ROWS(Range3),
OFFSET(Range2,COUNTA(A$1:A1)-ROWS(Range1)-ROWS(Range2),0,1,1),
"")))

Select Sheet4!A2 and fill down as far as needed.

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter into a column at least long enough to accommodate the values
in the three ranges:

=TRANSPOSE(MakeArray(range1,range2,range3,1)

Alan Beban

 

[Harlan Grove]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter into a column at least long enough to accommodate the values
in the three ranges:

=TRANSPOSE(MakeArray(range1,range2,range3,1)

Given that this is a syntax error (more left parentheses than right
parentheses), it's highly likely Alan didn't test this before posting it.

 

[DavidObeid]

Alan,

Thanks for your help. I tried your function

=TRANSPOSE(MakeArray(range1,range2,range3,1))

But all it did was copy the first number of range1 down the column.

What am I doing wrong?

 

[Harlan Grove]

=TRANSPOSE(MakeArray(range1,range2,range3,1))

But all it did was copy the first number of range1 down the column.

What am I doing wrong?

If the first cell containing the formula were A1, use this formula instead.

A1:
=INDEX(MakeArray(Range1,Range2,Range3,1),ROW())

Then fill A1 down as far as needed. Alan's formula (with your ending right
parenthesis correction) would fill the entire result range with a single
array formula. However, given that the sizes of Range1, Range2 and Range3
are variable, it's questionable whether using a single array formula is a
sensible approach.

 

[DavidObeid]

Thanks! Can I push my luck a little further and ask how I would get the
formula to treat blank cells as blank (and not zeros) ?

 

[Alan Beban]

If by blank you include cells that contain the empty string (i.e., ""), try

=IF(INDEX(makearray(Range1,Range2,Range3,1),ROW())=0,"",INDEX(makearray(Range1,Range2,Range3,1),ROW()))

Alan Beban

 

[Alan Beban]

It needs to be array entered, i.e., entered with Ctrl+Shft+Enter instead
of just Enter.

Alan Beban

 

[DavidObeid]

Dear Alan,

I know you are going to hate me for this, but blank cells ("") are t
be left blank whilst cells with a zero are to be recorded as zeros.

Is that possible

 

[Alan Beban]

David,

I can't get worked up enough over this stuff to hate anyone; I would,
though, have thought that after my previous response to this question
you might try the following on your own <g> (substitute Range1, Range2
and Range3 for arng, brng abd crng):

=IF(INDEX(makearray(arng,brng,crng,1),ROW())="","",INDEX(makearray(arng,brng,crng,1),ROW()))

Seems to work,
Alan Beban

 

[DavidObeid]

Dear Alan,

Thanks. Works perfectly.

Still getting my head around the INDEX function, so I wasn't quite sure
how to make it work for me. I seem to get dumber when working with
formulae that involve it.

Regards,

Dave