0

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.

Improve this question
3
  • 1
    Your question is a bit confusing. Instead of describing what you eat, please describe what you need to do in Excel (what data do you have, what is the desired output, what you've tried to achieve it, what is the exact problem). Also please only include the version you're really using, please note that questions on google-spreadsheets are off-topic here.
    – Máté Juhász
    Feb 23, 2017 at 11:29
  • 1
    Now it's maybe oversimplified:( Please keep the screenshot to help us understanding your data.
    – Máté Juhász
    Feb 23, 2017 at 12:18
  • @MátéJuhász story of my life :)) thanks. I'll edit later
    – BCLC
    Feb 23, 2017 at 12:33

2 Answers 2

Reset to default
1
+50

I create another sheet where i normalized the data.

I've changed the input method, added a parameter sheet and a dashboard.

Parameters and data

Plan

Tell me if that's what you want!

Improve this answer
2
  • Your plan can't be opened.. You should check this again
    – Michthan
    Mar 3, 2017 at 10:13
  • Awarding bounty and upvoting for effort but this doesn't quite answer my question. Please see Table 3. Also I added my own answer. Please let me know what you think
    – BCLC
    Mar 4, 2017 at 14:44
0

  1. Just use multiple cells.

  2. Lol this is actually pretty simple. In Excel this is the formula:

You don't need VB code try this. Put this in a cell in Col B and ARRAY enter it, see below on how to do that, and then drag down until it start returning blanks.

=IFERROR(INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)),ROW(1:1))),"")

where the question is

I have an expanding list of data in column A that comes from an external source. There are many blank gaps in the data in column A. I would like to be able to retrieve the values from column A and put them into column B without the gaps. I do not want to use sorting or filtering because the data is being updated all of the time.

Unfortunately the answer is array so you have to update everytime, I think. I forgot. Or maybe not but it's still so long.

In Google Sheets the formula is something to do with the filter function and is much simpler. I can't find the source anymore.

=IFERROR(FILTER(Sheetname!K:K, LEN(Sheetname!K:K)),"")

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .