#StackBounty: #microsoft-excel #microsoft-excel-2010 #microsoft-excel-2013 #scheduled-tasks #date-time Converting a weekly schedule int…

Bounty: 50

I’m tracking fruit consumption. In this example, I eat 5 fruits subdivided into 10 types (2 each) on certain days of the week at certain times of the day. Sometimes, I don’t eat a certain type of fruit on a certain day so I make up by eating 2 fruits the next day.


My code:


=IFERROR(TEXT(IF(COUNTIF($A5:$I5,"*"&O$1&"*")>1,"More than once!",INDEX($A$3:$I$14,2,MATCH("*"&O$1&"*",$A5:$I5,0))),"ddd, d mmm"),"")


Parts of my fruit consumption tracker:


First Part:


enter image description here


Last Part:


enter image description here


I want to end up with something like (Table 3):


enter image description here


So far all I got is (Table 4):


enter image description here


I got Table 4 by copying columns K through T on another sheet and then deleting the ‘blanks’, which aren’t really blanks according to go to special 😐

Table 3 to Table 4 is manual.


So:

  1. How might I get more than one date if a certain fruit is being consumed at the same hour of day apart from making duplicate time periods (consider if a certain fruit is being consumed at the same hour of day thrice in a week) ?

  2. How might I more efficiently compile all the dates together? I can’t seem to use go to special because it doesn’t consider the "" as blanks 😐

I think pivot tables can help, but I don’t exactly know what to do. I think VBAs/macros will help a lot, but I would like to first see what I can do without those.


Get this bounty!!!

Leave a Reply