I am using Excel 2003 and Visual Basic 6.5.

I have a table of London Underground stations

Part of that table is
A R S T U V W X Y Z AA AB AC
1 Station ... Bak Cen Cir Dis Ham Jub Met Nor Ove Pic Vic Wat
....
3 Acton Town ... 3 26
....
21 Barons Court ... 12 28

3, 12, 26, and 28 allow me to order stations topologically within a
line.
I hope I show that Action Town and Barons Court are connected by 2
lines, Dis(trict) and Pic(cadilly).
I can generate that 2 with

Evaluate("=sumproduct((R" & 3 & ":AC" & 3 & "<0)*(R" & 21 & ":AC" & 21 & "<0))")

[I avoid at number of double quote characters in that formula by using
the Excel 'identity' 0 = "". i.e. I use "< 0" in preference to "<
"""""]
I can quickly tell if there is more than one line connecting a given
pair of stations - the result of that formula is more than one.

I want to know for a given pair of stations known to be linked by more
than one line, what are the linking lines.
I think I would like a formula to produce the array
Bak Cen Cir Dis Ham Jub Met Nor Ove Pic Vic Wat
{"", "", "", "Dis", "", "", "", "", "", "Pic", "", ""}

and I would also like a formula to produce the array
{"Dis", "Pic"}

Obviously, I can find the matches by looping through row 3 or row 21 or
otherwise.

I would appreciate something more clever with looping "inside" Excel.
--
Walter Briscoe

On May 7, 2:33*pm, Walter Briscoe wrote:
I am using Excel 2003 and Visual Basic 6.5.

I have a table of London Underground stations

Part of that table is
* *A * * * * * * * *R * S * T * U * V * W * X * Y * Z * AA *AB *AC
*1 Station * * *... Bak Cen Cir Dis Ham Jub Met Nor Ove Pic Vic Wat
...
*3 Acton Town * ... * * * * * * * 3 * * * * * * * * * * *26
...
21 Barons Court ... * * * * * * *12 * * * * * * * * * * *28

3, 12, 26, and 28 allow me to order stations topologically within a
line.
I hope I show that Action Town and Barons Court are connected by 2
lines, Dis(trict) and Pic(cadilly).
I can generate that 2 with

Evaluate("=sumproduct((R" & 3 & ":AC" & 3 & "<0)*(R" & 21 & ":AC" & 21 & "<0))")

[I avoid at number of double quote characters in that formula by using
the Excel 'identity' 0 = "". i.e. I use "< 0" in preference to "<
"""""]
I can quickly tell if there is more than one line connecting a given
pair of stations - the result of that formula is more than one.

I want to know for a given pair of stations known to be linked by more
than one line, what are the linking lines.
I think I would like a formula to produce the array
*Bak Cen Cir Dis * *Ham Jub Met Nor Ove Pic * *Vic Wat
{"", "", "", "Dis", "", "", "", "", "", "Pic", "", ""}

and I would also like a formula to produce the array
{"Dis", "Pic"}

Obviously, I can find the matches by looping through row 3 or row 21 or
otherwise.

I would appreciate something more clever with looping "inside" Excel.
--
Walter Briscoe

Hard to tell without seeing the project.
"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."