|
Array or VB script?
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 |
Array or VB script?
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. "Justin" wrote: 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 |
Array or VB script?
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? "Toppers" wrote: 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. "Justin" wrote: 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 |
Array or VB script?
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 "Justin" wrote: 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? "Toppers" wrote: 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. "Justin" wrote: 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 |
Array or VB script?
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. "Justin" wrote: 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 "Justin" wrote: 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? "Toppers" wrote: 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. "Justin" wrote: 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 |
Array or VB script?
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 "Toppers" wrote: 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. "Justin" wrote: 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 "Justin" wrote: 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? "Toppers" wrote: 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. "Justin" wrote: 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 |
Array or VB script?
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),"") "Justin" wrote: 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 "Toppers" wrote: 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. "Justin" wrote: 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 "Justin" wrote: 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? "Toppers" wrote: 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. "Justin" wrote: 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 |
Array or VB script?
........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$2 50=F2,A2:A$250)),"") "Justin" wrote: 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 "Toppers" wrote: 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. "Justin" wrote: 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 "Justin" wrote: 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? "Toppers" wrote: 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. "Justin" wrote: 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 |
Array or VB script?
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). "Justin" wrote: 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 "Toppers" wrote: 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. "Justin" wrote: 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 "Justin" wrote: 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? "Toppers" wrote: 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. "Justin" wrote: 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 |
| All times are GMT +1. The time now is 08:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com