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.
=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:
I want to end up with something like (Table 3):
So far all I got is (Table 4):
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.
- 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) ?
- 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.