I have this input table tracking time spent on various activities over a year (first table below). I want to show it in an output table with rows as days and time slots as columns (see 2nd table below) to get an overview of how time was spent.
I can use IndexMatchMatch in I2 for the Aug 3 type activity as the times neatly coincide with the output table.
For the Aug 4 activities I have a problem as I want to match if the time in output table row 1 is equal to/between the times in the input table columns B & C.
I've tried loads of different options & am stumped.
Surely this should be doable?
Would really appreciate help from the experienced index/matchers here.
Thanks in advance.
Input Table (c 1 yr's worth of data - not always in order)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aug 3, 2017[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]Meetings[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aug 4, 2017[/TD]
[TD]1:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]Thinking[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Aug 4, 2017[/TD]
[TD]5:00 PM[/TD]
[TD]8:00 PM[/TD]
[TD]Meetings[/TD]
[/TR]
</tbody>[/TABLE]
Desired Output Table (I'll use conditional formatting to show activities by color)
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]2:00 PM[/TD]
[TD]3:00 PM[/TD]
[TD]4:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]6:00 PM[/TD]
[TD]7:00 PM[/TD]
[TD]8:00 PM[/TD]
[TD]9:00 PM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aug 3, 2017[/TD]
[TD]Meetings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aug 4, 2017[/TD]
[TD][/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Meetings[/TD]
[TD]Meetings[/TD]
[TD]Meetings[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 218px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]Aug 3, 2017[/TD]
[/TR]
</tbody>[/TABLE]
I can use IndexMatchMatch in I2 for the Aug 3 type activity as the times neatly coincide with the output table.
For the Aug 4 activities I have a problem as I want to match if the time in output table row 1 is equal to/between the times in the input table columns B & C.
I've tried loads of different options & am stumped.
Surely this should be doable?
Would really appreciate help from the experienced index/matchers here.
Thanks in advance.
Input Table (c 1 yr's worth of data - not always in order)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aug 3, 2017[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]Meetings[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aug 4, 2017[/TD]
[TD]1:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]Thinking[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Aug 4, 2017[/TD]
[TD]5:00 PM[/TD]
[TD]8:00 PM[/TD]
[TD]Meetings[/TD]
[/TR]
</tbody>[/TABLE]
Desired Output Table (I'll use conditional formatting to show activities by color)
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]2:00 PM[/TD]
[TD]3:00 PM[/TD]
[TD]4:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]6:00 PM[/TD]
[TD]7:00 PM[/TD]
[TD]8:00 PM[/TD]
[TD]9:00 PM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aug 3, 2017[/TD]
[TD]Meetings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aug 4, 2017[/TD]
[TD][/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Meetings[/TD]
[TD]Meetings[/TD]
[TD]Meetings[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 218px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]Aug 3, 2017[/TD]
[/TR]
</tbody>[/TABLE]