expanding SUMPRODUCT

[Walter Briscoe]

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.

 

[Don Guillett]

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.

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."