Hello,
I am trying to create a matrix that includes specific data from different Excel workbooks. However, since I don't know how to use macros, I'm going to transfer data from each workbook to the appropriate day column in my matrix, one by one.
I will use trains carrying food products as my example. There are "fruit trains" carrying fruits, and there are other trains (like rice or beans). I only want to include data about fruit trains in my matrix. By train number, and the day of week the train operated, I want to sum the total minutes of delay that each train experienced as it traveled to it's final destination. I cannot change the format/layout of my final matrix.
I think I need to use combinations of sumifs and sumproduct, but I haven't found something that works for my specific situation.
MATRIX: Fruit Train Delays[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]8/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]8/2/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]8/3/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Train #[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]M-F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]M-F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SatSun[/TD]
[/TR]
[TR]
[TD]M-F[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]561[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]7[/TD]
[TD]formula?[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SatSun[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]561[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]formula?[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]M-F[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]562[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]formula?[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SatSun[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]562[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]M-F[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]564[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SatSun[/TD]
[TD="align: center"]1564[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]M-F[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]763[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SatSun[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]763[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]8/1/19 Report[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Train #[/TD]
[TD]Product[/TD]
[TD]MinsDelayed[/TD]
[/TR]
[TR]
[TD]561[/TD]
[TD]Grapes[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]561[/TD]
[TD]Kiwis[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]562[/TD]
[TD]Apples[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]562[/TD]
[TD]Rice[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]564[/TD]
[TD]Beans[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1564[/TD]
[TD]Apples[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]763[/TD]
[TD]Kiwis[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]763[/TD]
[TD]Rice[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Fruits List:[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Grape[/TD]
[/TR]
[TR]
[TD="class: xl65"]Kiwi[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apple[/TD]
[/TR]
[TR]
[TD="class: xl65"]Orange[/TD]
[/TR]
[TR]
[TD="class: xl65"]Grape
[/TD]
[/TR]
</tbody>[/TABLE]
Any help would be much appreciated! Thank you.
I am trying to create a matrix that includes specific data from different Excel workbooks. However, since I don't know how to use macros, I'm going to transfer data from each workbook to the appropriate day column in my matrix, one by one.
I will use trains carrying food products as my example. There are "fruit trains" carrying fruits, and there are other trains (like rice or beans). I only want to include data about fruit trains in my matrix. By train number, and the day of week the train operated, I want to sum the total minutes of delay that each train experienced as it traveled to it's final destination. I cannot change the format/layout of my final matrix.
I think I need to use combinations of sumifs and sumproduct, but I haven't found something that works for my specific situation.
MATRIX: Fruit Train Delays[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]8/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]8/2/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]8/3/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Train #[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]M-F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]M-F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SatSun[/TD]
[/TR]
[TR]
[TD]M-F[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]561[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]7[/TD]
[TD]formula?[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SatSun[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]561[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]formula?[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]M-F[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]562[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]formula?[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SatSun[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]562[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]M-F[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]564[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SatSun[/TD]
[TD="align: center"]1564[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]M-F[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]763[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SatSun[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, align: right"]763[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]8/1/19 Report[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Train #[/TD]
[TD]Product[/TD]
[TD]MinsDelayed[/TD]
[/TR]
[TR]
[TD]561[/TD]
[TD]Grapes[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]561[/TD]
[TD]Kiwis[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]562[/TD]
[TD]Apples[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]562[/TD]
[TD]Rice[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]564[/TD]
[TD]Beans[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1564[/TD]
[TD]Apples[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]763[/TD]
[TD]Kiwis[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]763[/TD]
[TD]Rice[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Fruits List:[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Grape[/TD]
[/TR]
[TR]
[TD="class: xl65"]Kiwi[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apple[/TD]
[/TR]
[TR]
[TD="class: xl65"]Orange[/TD]
[/TR]
[TR]
[TD="class: xl65"]Grape
[/TD]
[/TR]
</tbody>[/TABLE]
Any help would be much appreciated! Thank you.