Array or VB script?

[Guest]

Hi,

I have a list of data comprising movement events and their starting and
ending locations that looks like this (its much longer):


ID TRAIN Start Finish ORIG DEST 1ST CONNECT
1 A4589 1 2/11/07 0:01 2/11/07 3:25 MARKIL BATCRK 18
2 H5306 1 2/11/07 0:01 2/11/07 2:31 EFLINT PTHURO
3 H5316 1 2/11/07 0:01 2/11/07 2:16 PTHURO EFLINT
4 Q1957 1 2/11/07 0:02 2/12/07 7:00 NEWORL CHIINT
5 A4108 1 2/11/07 0:05 2/11/07 7:30 WISRAP FONLAC
6 M3437 1 2/11/07 0:30 2/11/07 21:00 JACKMS SYMING
7 A4038 1 2/11/07 1:00 2/11/07 8:55 STEPOI SITASC
8 A4198 1 2/11/07 1:00 2/11/07 4:40 FONLAC GREBAY
9 A4329 1 2/11/07 1:15 2/12/07 11:20 DECATU MEMPHI
10 M3017 1 2/11/07 1:15 2/11/07 20:50 BATROU MEMPHI
11 A4158 1 2/11/07 1:15 2/11/07 6:30 GREBAY STEPOI
12 A4317 1 2/11/07 1:30 2/12/07 10:55 MEMPHI DECATU
13 A4379 1 2/11/07 2:30 2/11/07 6:25 CHAMPA MARKIL
14 A4426 1 2/11/07 3:30 2/11/07 6:30 PONTIA FLAROC
15 A4887 1 2/11/07 4:00 2/11/07 14:00 JACKMS MOBILE
16 M3207 1 2/11/07 4:30 2/11/07 23:59 MEMPHI GEISMA
17 E2516 1 2/11/07 4:50 2/11/07 19:15 DETROI GIBSON
18 A4566 1 2/11/07 5:00 2/11/07 14:00 BATCRK PONTIA


I am trying to figure out how to create a formula that evaluates subsequent
events (IDs) in the list and returns the ID of the first subsequent event
who's departure location matches the arrival location of the event being
evaluated, and the start time of the subsequent event is >= of the finish
time of the event being evaluated. as in the example, the solution for line
1 would be 18.

The actual spreadsheet I am working on contains 14 days of scheduled events
(about 1600 lines) - I have been able to generate about 3 days of data using
a 240 x 240 table, but I run out memory and end up with a 50 meg spreadsheet
that doesn't open when I try to expand the table to multiple spreadsheets in
a workbook...I'm hoping someone with VBA knowledge can offer some advice?

Thanks in advance,

JDA

 

[Guest]

Try: (using your sample data ranges)

=SUMPRODUCT(--($E$2:$E$19=F2),--($C$2:$C$19>=D2),($A$2:$A$19))

Or

=IF(SUMPRODUCT(--($E$2:$E$19=F2),--($C$2:$C$19>=D2),($A$2:$A$19))>0,SUMPRODUCT(--($E$2:$E$19=F2),--($C$2:$C$19>=D2),($A$2:$A$19)),"")

leaves blanks where no matches


Change ranges to suit.

 

[Guest]

Toppers,

Thanks for taking a look at my problem...for some reason, when I expand the
range to the entire list, I get numbers that make no sense:

ID TRAIN Start Finish ORIG DEST 1ST CONNECT
1 A4589 1 2/11/07 0:01 2/11/07 3:25 MARKIL BATCRK 1781
2 H5306 1 2/11/07 0:01 2/11/07 2:31 EFLINT PTHURO 440
3 H5316 1 2/11/07 0:01 2/11/07 2:16 PTHURO EFLINT 437
4 Q1957 1 2/11/07 0:02 2/12/07 7:00 NEWORL CHIINT 979
5 A4108 1 2/11/07 0:05 2/11/07 7:30 WISRAP FONLAC 1836
6 M3437 1 2/11/07 0:30 2/11/07 21:00 JACKMS SYMING
7 A4038 1 2/11/07 1:00 2/11/07 8:55 STEPOI SITASC 428
8 A4198 1 2/11/07 1:00 2/11/07 4:40 FONLAC GREBAY 838
9 A4329 1 2/11/07 1:15 2/12/07 11:20 DECATU MEMPHI 1430
10 M3017 1 2/11/07 1:15 2/11/07 20:50 BATROU MEMPHI 1671
11 A4158 1 2/11/07 1:15 2/11/07 6:30 GREBAY STEPOI 1129
12 A4317 1 2/11/07 1:30 2/12/07 10:55 MEMPHI DECATU 377
13 A4379 1 2/11/07 2:30 2/11/07 6:25 CHAMPA MARKIL 805
14 A4426 1 2/11/07 3:30 2/11/07 6:30 PONTIA FLAROC 1496
15 A4887 1 2/11/07 4:00 2/11/07 14:00 JACKMS MOBILE 350
16 M3207 1 2/11/07 4:30 2/11/07 23:59 MEMPHI GEISMA 284
17 E2516 1 2/11/07 4:50 2/11/07 19:15 DETROI GIBSON
18 A4566 1 2/11/07 5:00 2/11/07 14:00 BATCRK PONTIA 473
19 E2816 1 2/11/07 6:00 2/12/07 3:05 FLAROC GIBCIT
20 E2957 1 2/11/07 6:00 2/11/07 11:50 JACKMS MEMPHI 1715
21 A4806 1 2/11/07 6:30 2/11/07 16:20 BATCRK FLAROC 1461
22 U7458 1 2/11/07 7:00 2/11/07 16:30 ESCANA ESCANA 973
23 M3368 1 2/11/07 7:30 2/13/07 4:35 FONLAC MEMPHI 865
24 A4078 1 2/11/07 7:30 2/11/07 16:35 FONLAC WAUSAU 278
25 M3389 1 2/11/07 7:45 2/12/07 4:15 WATEIA MARKIL 680
26 M3347 1 2/11/07 8:00 2/12/07 14:30 FULTON FERGMS
27 M3949 1 2/11/07 8:00 2/12/07 7:15 HARVIL MACYD
28 M3846 1 2/11/07 8:00 2/12/07 6:00 AILIJC MACYD
29 A4168 1 2/11/07 8:00 2/11/07 12:00 STEPOI GREBAY 838
30 A4539 1 2/11/07 9:00 2/11/07 18:30 PROVIS FONLAC 1789
31 A4549 1 2/11/07 9:00 2/11/07 14:20 YARCEN BATCRK 1742
32 A4343 1 2/11/07 9:30 2/11/07 22:30 WINDSO MACYD
33 A4088 1 2/11/07 9:30 2/11/07 17:30 WAUSAU FONLAC 1789
34 M3458 1 2/11/07 10:00 2/12/07 0:35 SUPERI SYMING
35 E2466 1 2/11/07 10:00 2/11/07 13:30 FLAROC LANGOH 353
36 M3197 1 2/11/07 10:45 2/13/07 12:45 GEISMA CHAMPA 422
37 M3929 1 2/11/07 11:00 2/12/07 8:55 BLUISL MACYD
38 U7418 1 2/11/07 11:30 2/11/07 22:00 ESCANA ESCANA 973
39 M3303 1 2/11/07 12:00 2/11/07 20:00 SARNIA FTERIE
40 A4068 1 2/11/07 13:30 2/11/07 22:00 NEWBRI STEPOI 1045
41 M3357 1 2/11/07 13:40 2/12/07 4:20 JACKMS FULTON 262
42 M3909 1 2/11/07 14:30 2/12/07 15:35 GLENYD MACYD
43 Q1446 1 2/11/07 15:00 2/12/07 0:30 OAKJCT MACYD
44 M3027 1 2/11/07 15:15 2/12/07 9:30 MEMPHI BATROU 379
45 A4897 1 2/11/07 15:15 2/12/07 1:45 MOBILE JACKMS 1305
46 E2476 1 2/11/07 15:30 2/11/07 18:30 LANGOH FLAROC 1461
47 M3319 1 2/11/07 16:30 2/12/07 13:30 CHAMPA FONLAC 1396
48 A4119 1 2/11/07 17:00 2/12/07 19:15 CLEARI SSMON
49 U7048 1 2/11/07 18:00 2/12/07 5:00 ALLEWI ALLEWI 313
50 Q1949 1 2/11/07 18:15 2/12/07 21:30 CHIINT NEWORL 367
51 M3953 1 2/11/07 18:20 2/12/07 10:30 SARNIA CICERO
52 M3379 1 2/11/07 18:30 2/12/07 15:00 MARKIL WATEIA 167
53 A4428 1 2/11/07 18:30 2/12/07 13:40 GREBAY CLEARI 893
54 M3576 1 2/11/07 19:00 2/15/07 16:40 BATCRK PRIGEO
55 A4058 1 2/11/07 19:00 2/12/07 2:15 STEPOI NEWBRI 586
56 Q1489 1 2/11/07 19:30 2/14/07 7:00 CHIINT HALIFA
57 U7028 1 2/11/07 20:00 2/12/07 4:00 SUSSWI SUSSWI 329

Any suggestions?

 

[Guest]

what I mean by makes no sense: this formula is summing the ID colum values
where the ORIG field matches the 1st DEST field; I just want it to return the
ID value of the first subsequent record that meets the conditions I listed
previously...?

JDA

 

[Guest]

Justin,
Yes .. the big flaw in my logic ... if there mutiple entries
for the same condition, the entries will be summed.

VBA macro might be the best answer.

I'll have a think about this.

Apologies again.

 

[Guest]

I can use the following array to give me the first match based on stations,
but I cannot figure out how to code the time logic:

{=MIN(IF(E2:E$250=F2,A2:A$250))}

any ideas how I could write that into this formula?

JDA

 

[Guest]

Justin,
Try this: it works for the first occurence but I haven't tested
it further.

=IF(INDIRECT("C" &MIN(IF(E2:E$250=F2,A2:A$250))+1)>D2,INDIRECT("A"
&MIN(IF(E2:E$250=F2,A2:A$250))+1),"")

 

[Guest]

........Perhaps even this ...only works if IDs are sequential i.e 1 to N,
with non missing as the ID is used to determine the cell address. I think you
getb the idea!!

=IF(INDIRECT("C"
&MIN(IF(E2:E$250=F2,A2:A$250))+1)>D2,MIN(IF(E2:E$250=F2,A2:A$250)),"")

 

[Guest]

Justin,
Have you got a sample file (reasonable size) that you could
send me: I've a VBA routine which looks OK on your limited data but I would
like to test further.

toppers at NOSPAMjohntopley.fsnet.co.uk

(Remove NOSPAM).