Need formula: One event occurs earlier than another in table

[TOguy]

Hi there,

Hoping someone can help me out with my example below. I want to know if "Billy" had an apple before he had either an orange or banana first using the table below. (side note: I've generated the table using pivot table based on the raw data - not sure if that changes anything). A simple "yes", "no" response would suffice.
Thanks so much for any help

Person Date Apple Orange Banana
Billy Jan/1/2015 - 4 -
Billy Feb/1/2015 - 4 -
Billy Mar/1/2015 4 - -
Billy Apr/1/2015 4 - -
Billy May/1/2015 - - 4
Billy Jun/1/2015 - - 4

 

[steve the fish]

Hi. As long as there is only one fruit per day then this works with your table in A1:E7

It needs array entering so when you place it in a cell press CNTL-SHIFT then ENTER. Not just ENTER. There may be a simpler way!

=IF(VLOOKUP(MIN(IF($A$2:$A$100="Billy",$B$2:$B$100)),$B$2:$E$100,MATCH("Apple",$B$1:$E$1,0),0)>0,"Yes","No")

 

[TOguy]

Thanks very much for the formula. I've tried putting it in column F and doing the array entering, but it's just returning #N/A results when I drag the formula down from F2:F7. Am I putting it in the wrong spot?

Thanks!

 

[TOguy]

Thanks very much for the formula. I've tried putting it in column F and doing the array entering, but it's just returning #N/A results when I drag the formula down from F2:F7. Am I putting it in the wrong spot?

Thanks!

 

[steve the fish]

Are you getting a result in F1? Why would you drag it down? You asked if "Billy" had an apple before he had either an orange or banana. That answer would always be the same regardless so it doesn't need to be dragged down.

 

[TOguy]

No, #N/A in F1 as well. You're right, I don't need to drag it down. Just wasn't sure if that's how it was structured so I tried it with my fingers crossed!
Any ideas? Thanks!

 

[steve the fish]

First check for array enter. Look for curly brackets around your formula in the formula bar {}. Then does any cell in A2:A100 equal "Billy". Then does any cell in B1:E1 equal "Apple"?

Try these:

=COUNTIF(A2:A100,"Billy")
=COUNTIF(B1:E1,"Apple")

Do any of them equal 0?

 

[TOguy]

Yes, it has the curly brackets around the formula. The "=COUNTIF" formulas both return a result ("6" for Billy & "1" for apple)
Any way I can send you an email with the excel file, if possible? I feel like I'm missing something really simple here...
Thanks!

 

[steve the fish]

Only other thing is that your dates are not real dates. If they aren't you will need to change them to real dates. With the best will in the world excel doesn't know Jan/1/2015 is earlier than Feb/1/2015 if they are text entries.

 

[TOguy]

Bingo! That was the issue - switched to "date" from "text" and it worked right away. Thanks very much!!