Intersect operation
 

I hope readers here can help.

I am having trouble developing an array formula in a worksheet.
Once I have the appropriate symbols, I would like to run the
calculation without writing to a worksheet. I suspect that is
impossible.

I have 2 rows in a worksheet containing
Alscot Road stop U 1 78 N1
Spa Road stop V 1 78 N1

In column 1 of the third row, I have the array formula
=R[-2]C[1]:R[-2]C[3] R[-1]C[1]:R[-1]C[3]
I expect the formula to produce
1 78 N1
because the values in the two ranges R[-2]C[1]:R[-2]C[3] and
R[-1]C[1]:R[-1]C[3] are the same
(My data represents London Bus services at adjacent stops.)
However, it produces
#NULL!

When I have an appropriate formula, I will transform the result to 1,
78, N1

Once I have a formula which works for adjacent stops, I will apply it
to distant stops which share one or more routes.

Thanks!
--
Walter Briscoe

Intersect operation
 

In message of Wed, 22 Mar 2017
10:57:39 in microsoft.public.excel.programming, Walter Briscoe
writes
I hope readers here can help.

I am having trouble developing an array formula in a worksheet.
Once I have the appropriate symbols, I would like to run the
calculation without writing to a worksheet. I suspect that is
impossible.

I have 2 rows in a worksheet containing
Alscot Road stop U 1 78 N1
Spa Road stop V 1 78 N1

In column 1 of the third row, I have the array formula
=R[-2]C[1]:R[-2]C[3] R[-1]C[1]:R[-1]C[3]
I expect the formula to produce
1 78 N1
because the values in the two ranges R[-2]C[1]:R[-2]C[3] and
R[-1]C[1]:R[-1]C[3] are the same
(My data represents London Bus services at adjacent stops.)
However, it produces
#NULL!

When I have an appropriate formula, I will transform the result to 1,
78, N1

Once I have a formula which works for adjacent stops, I will apply it
to distant stops which share one or more routes.

Thanks!

My apologies. The space operator does not do as I thought.
--
Walter Briscoe