Forum Discussion
How do I pull data from one sheet into another based on multiple matching criteria
I have a book1(monthly vehicle toll tag statement) table1. i need to pull amount for particular plaza on particular date for entire month (table2).
Any help would be much appreciated.
| Transaction Date Time | Licence Plate No | Plaza Code | Amount(DR) |
| 17-07-2023 21:26 | 123 | 219222 | 370 |
| 17-07-2023 20:25 | 123 | 219221 | 372 |
| 17-07-2023 19:35 | 123 | 218221 | 570 |
| 17-07-2023 18:41 | 123 | 362232 | 365 |
| 17-07-2023 17:43 | 123 | 362249 | 375 |
| 17-07-2023 16:42 | 123 | 362234 | 270 |
| 17-07-2023 16:29 | 123 | 362228 | 375 |
| 17-07-2023 15:50 | 123 | 362242 | 230 |
| 16-07-2023 02:00 | 123 | 362242 | 230 |
| 16-07-2023 01:46 | 123 | 362228 | 375 |
| 16-07-2023 01:31 | 123 | 362234 | 270 |
| 16-07-2023 00:43 | 123 | 362249 | 375 |
| 15-07-2023 23:47 | 123 | 362232 | 365 |
| 15-07-2023 22:44 | 123 | 218221 | 570 |
| 15-07-2023 21:52 | 123 | 219221 | 370 |
| 15-07-2023 20:50 | 123 | 219222 | 180 |
| Amount | ||||||||||||||||||
| Licence Plate No | vehicle in date & time | vehicle out date & time | 219222 | 219221 | 218221 | 362232 | 362249 | 362234 | 362228 | 362242 | 362242 | 362228 | 362234 | 362249 | 362232 | 218221 | 219221 | 219222 |
| 123 | 15-07-2023 12:50 | 17-07-2023 19:43 | ||||||||||||||||
| 123 | 25-07-2023 12:50 | 27-07-2023 19:43 |
6 Replies
SUMIFS provide an alternative formulation that will allow the results to be generated with a single dynamic array formula.
= SUMIFS(amount, licencePlate, plate, transactionDate, ">="&dateIn, transactionDate, "<="&dateOut, PlazaCode, code )Thanks for the response.
Sno1&2 in Table2 are sample results. data entered manually from table 1. For likewise result in sno3&4 function required.
Serval hundred are there, manually data entry taking days to complete.
note: table 1 and table 2 are in different workbook
TABLE 1:
Transaction Date Time Licence Plate No Plaza Code Amount(DR) 30-05-2023 16:18 123 356023 165 30-05-2023 10:43 123 120001 245 30-05-2023 09:19 123 120002 140 30-05-2023 08:59 123 118002 145 30-05-2023 07:24 123 118001 145 30-05-2023 07:02 123 116001 75 30-05-2023 06:10 123 116003 265 29-05-2023 13:06 123 116003 525 29-05-2023 11:38 123 116001 155 29-05-2023 11:28 123 118001 295 29-05-2023 09:54 123 118002 295 29-05-2023 09:35 123 120002 285 29-05-2023 08:07 123 120001 245 28-05-2023 14:53 123 356023 165 28-05-2023 08:47 123 257003 250 27-05-2023 13:00 123 0 27-05-2023 12:58 123 239700 405 27-05-2023 11:53 123 360027 0 27-05-2023 11:46 123 360020 0 27-05-2023 11:42 123 360021 0 27-05-2023 11:20 123 360026 198 27-05-2023 07:47 123 059002 150 27-05-2023 06:55 123 059001 85 26-05-2023 09:46 123 059001 165 26-05-2023 08:10 123 059002 300 25-05-2023 18:08 123 360026 0 25-05-2023 17:45 123 360021 127 25-05-2023 17:40 123 360020 0 25-05-2023 17:31 123 360027 71 25-05-2023 16:46 123 239700 405 24-05-2023 19:36 123 257003 250 24-05-2023 10:48 123 356023 165 23-05-2023 19:46 123 120001 120 23-05-2023 18:17 123 120002 140 23-05-2023 17:56 123 118002 145 23-05-2023 16:27 123 118001 145 23-05-2023 16:06 123 116001 75 23-05-2023 14:52 123 116003 265 23-05-2023 10:10 123 116003 525 23-05-2023 08:48 123 116001 155 23-05-2023 08:38 123 118001 295 23-05-2023 07:11 123 0 23-05-2023 07:10 123 118002 295 23-05-2023 06:50 123 120002 285 22-05-2023 20:47 123 120001 245 22-05-2023 14:31 123 356023 165 21-05-2023 16:09 123 257002 260 21-05-2023 14:59 123 257001 310 21-05-2023 13:46 123 356008 520 21-05-2023 12:05 123 356007 550 21-05-2023 10:48 123 356006 550 19-05-2023 20:16 123 356006 550 19-05-2023 19:01 123 0 19-05-2023 19:00 123 356007 550 19-05-2023 17:29 123 356008 520 19-05-2023 16:14 123 257001 310 19-05-2023 15:00 123 257002 260 19-05-2023 08:27 123 356023 165 18-05-2023 17:47 123 120001 245 18-05-2023 16:31 123 120002 285 18-05-2023 16:12 123 118002 295 18-05-2023 13:59 123 118001 145 18-05-2023 13:37 123 116001 155 18-05-2023 12:22 123 116003 265 17-05-2023 18:23 123 0 17-05-2023 18:21 123 116003 525 17-05-2023 16:31 123 118001 295 17-05-2023 14:51 123 118002 295 17-05-2023 14:29 123 120002 285 17-05-2023 12:55 123 120001 245 17-05-2023 06:42 123 356023 165 15-05-2023 06:46 123 356023 165 14-05-2023 15:51 123 120001 245 14-05-2023 14:23 123 120002 285 14-05-2023 14:04 123 118002 295 14-05-2023 12:36 123 118001 145 14-05-2023 12:11 123 116001 75 14-05-2023 09:37 123 116003 265 13-05-2023 17:25 123 116003 525 13-05-2023 15:27 123 116001 155 13-05-2023 15:13 123 0 13-05-2023 15:09 123 118001 295 13-05-2023 13:27 123 118002 295 13-05-2023 13:07 123 120002 285 13-05-2023 11:22 123 120001 245 12-05-2023 19:02 123 356023 165 12-05-2023 08:19 123 257002 260 12-05-2023 07:02 123 257001 310 11-05-2023 20:12 123 356008 520 11-05-2023 18:33 123 356007 550 11-05-2023 17:24 123 356006 550 09-05-2023 10:53 123 356006 550 09-05-2023 09:41 123 0 09-05-2023 09:40 123 356007 550 09-05-2023 07:58 123 356008 520 09-05-2023 06:45 123 257001 310 08-05-2023 19:42 123 257002 260 07-05-2023 12:15 123 257003 250 05-05-2023 20:02 123 257003 250 TABLE 2:
S No Licence Plate No Date 356023 120001 120002 118002 118001 116001 116003 116003 116001 118001 118002 120002 120001 356023 Amount 1 123 12-05-2023 165 245 285 295 295 155 525 265 75 145 295 285 245 165 3440 2 123 16-05-2023 165 245 285 295 295 0 525 265 155 145 295 285 245 165 3365 3 123 22-05-2023 3020 4 123 28-05-2023 3145
=SUMPRODUCT(($B$2:$B$17=$A20)*($A$2:$A$17>=$B20)*($A$2:$A$17<=$C20)*($C$2:$C$17=D$19)*$D$2:$D$17)Does this return the intended result?
OliverScheurich Thanks for the response.
Sno1&2 in Table2 are sample results. data entered manually from table 1. For likewise result in sno3&4 function required.
Serval hundred are there, manually data entry taking days to complete.
TABLE 1:
Transaction Date Time Licence Plate No Plaza Code Amount(DR) 30-05-2023 16:18 123 356023 165 30-05-2023 10:43 123 120001 245 30-05-2023 09:19 123 120002 140 30-05-2023 08:59 123 118002 145 30-05-2023 07:24 123 118001 145 30-05-2023 07:02 123 116001 75 30-05-2023 06:10 123 116003 265 29-05-2023 13:06 123 116003 525 29-05-2023 11:38 123 116001 155 29-05-2023 11:28 123 118001 295 29-05-2023 09:54 123 118002 295 29-05-2023 09:35 123 120002 285 29-05-2023 08:07 123 120001 245 28-05-2023 14:53 123 356023 165 28-05-2023 08:47 123 257003 250 27-05-2023 13:00 123 0 27-05-2023 12:58 123 239700 405 27-05-2023 11:53 123 360027 0 27-05-2023 11:46 123 360020 0 27-05-2023 11:42 123 360021 0 27-05-2023 11:20 123 360026 198 27-05-2023 07:47 123 059002 150 27-05-2023 06:55 123 059001 85 26-05-2023 09:46 123 059001 165 26-05-2023 08:10 123 059002 300 25-05-2023 18:08 123 360026 0 25-05-2023 17:45 123 360021 127 25-05-2023 17:40 123 360020 0 25-05-2023 17:31 123 360027 71 25-05-2023 16:46 123 239700 405 24-05-2023 19:36 123 257003 250 24-05-2023 10:48 123 356023 165 23-05-2023 19:46 123 120001 120 23-05-2023 18:17 123 120002 140 23-05-2023 17:56 123 118002 145 23-05-2023 16:27 123 118001 145 23-05-2023 16:06 123 116001 75 23-05-2023 14:52 123 116003 265 23-05-2023 10:10 123 116003 525 23-05-2023 08:48 123 116001 155 23-05-2023 08:38 123 118001 295 23-05-2023 07:11 123 0 23-05-2023 07:10 123 118002 295 23-05-2023 06:50 123 120002 285 22-05-2023 20:47 123 120001 245 22-05-2023 14:31 123 356023 165 21-05-2023 16:09 123 257002 260 21-05-2023 14:59 123 257001 310 21-05-2023 13:46 123 356008 520 21-05-2023 12:05 123 356007 550 21-05-2023 10:48 123 356006 550 19-05-2023 20:16 123 356006 550 19-05-2023 19:01 123 0 19-05-2023 19:00 123 356007 550 19-05-2023 17:29 123 356008 520 19-05-2023 16:14 123 257001 310 19-05-2023 15:00 123 257002 260 19-05-2023 08:27 123 356023 165 18-05-2023 17:47 123 120001 245 18-05-2023 16:31 123 120002 285 18-05-2023 16:12 123 118002 295 18-05-2023 13:59 123 118001 145 18-05-2023 13:37 123 116001 155 18-05-2023 12:22 123 116003 265 17-05-2023 18:23 123 0 17-05-2023 18:21 123 116003 525 17-05-2023 16:31 123 118001 295 17-05-2023 14:51 123 118002 295 17-05-2023 14:29 123 120002 285 17-05-2023 12:55 123 120001 245 17-05-2023 06:42 123 356023 165 15-05-2023 06:46 123 356023 165 14-05-2023 15:51 123 120001 245 14-05-2023 14:23 123 120002 285 14-05-2023 14:04 123 118002 295 14-05-2023 12:36 123 118001 145 14-05-2023 12:11 123 116001 75 14-05-2023 09:37 123 116003 265 13-05-2023 17:25 123 116003 525 13-05-2023 15:27 123 116001 155 13-05-2023 15:13 123 0 13-05-2023 15:09 123 118001 295 13-05-2023 13:27 123 118002 295 13-05-2023 13:07 123 120002 285 13-05-2023 11:22 123 120001 245 12-05-2023 19:02 123 356023 165 12-05-2023 08:19 123 257002 260 12-05-2023 07:02 123 257001 310 11-05-2023 20:12 123 356008 520 11-05-2023 18:33 123 356007 550 11-05-2023 17:24 123 356006 550 09-05-2023 10:53 123 356006 550 09-05-2023 09:41 123 0 09-05-2023 09:40 123 356007 550 09-05-2023 07:58 123 356008 520 09-05-2023 06:45 123 257001 310 08-05-2023 19:42 123 257002 260 07-05-2023 12:15 123 257003 250 05-05-2023 20:02 123 257003 250 TABLE 2:
S No Licence Plate No Date 356023 120001 120002 118002 118001 116001 116003 116003 116001 118001 118002 120002 120001 356023 Amount 1 123 12-05-2023 165 245 285 295 295 155 525 265 75 145 295 285 245 165 3440 2 123 16-05-2023 165 245 285 295 295 0 525 265 155 145 295 285 245 165 3365 3 123 22-05-2023 3020 4 123 28-05-2023 3145 I would suggest changing the data on Tabelle1 to be an Excel Table so that it scales with the input data. Similarly any manual input fields on output table. I note that the 'in' and 'out' times have disappeared for some reason. The 'Plaza' codes would be created as a dynamic array (unique values, sorted) and referenced by the SUMIFS formula as a dynamic range.
I have nothing to contribute on separate workbooks; I am fortunate to be able to avoid them. It may well be that the source workbook must be open if the destination sheet is to work
