Find a First Occurence in a Column

[Kevin Barrios]

As a manufacturer, we need to keep track of what items we made, how much, and
when. So, to simplify the complex task, as we have hundreds of products over
many years, look at the example below:

A B C
1 Date Red Bicycles Blue Bicycles
2 1/3/10
3 1/4/10 12
4 1/5/10 4
5 1/6/10 2
6 1/7/10
7 1/8/10 8

So, in effect, I'd like to find out that we first made 12 Red Bicycles on
1/4/10 and that the last time we made Red Bicycles was on 1/6/10 and made 2
of them. Same thing with Blue Bicycles, first made 4 on the 5th and last
time we made blue ones was on the 8th with 8 made.

Any suggestions would be appreciated and I'll click "yes it helped" to any
of your posts that help.

-Kevin

 

[Eva]

Hi
This formula checks data in column C (Bicycles) and returns last occurances

=INDIRECT("c"&MAX(IF(C2:C10>0,ROW(A2:A10))))

Apply same to Red Bicycles Blue

 

[Paul C]

A B C
1 Date Red Blue
2 1/3/2010
3 1/4/2010 12
4 1/5/2010 4
5 1/6/2010 2
6 1/7/2010
7 1/8/2010 8
8
9 Red First Blue First
10 1/4/2010 1/5/2010
11 12 4
12 Red Last Blue Last
13 1/6/2010 1/8/2010
14 2 8

For A10 use an array formula
{=INDEX($A$2:$A$7,MATCH(TRUE,B2:B7>0,0))}
Type the formula without { and } and use Ctrl+Shift+Enter to enter and the
curly brackets will appear - you cannot type these yourself and have the
formula work.
You must Ctrl+Shift+Enter whenever you edit the formula
This can be copied over

For A11 use a simple VLOOKUP with a MATCH for column number (so you can copy
w/o changing the column)
=VLOOKUP(B10,$A$2:$C$7,MATCH(B1,$A$1:$C$1,0))
This can be copied over

For A13 another array formula (same note as for A10)
{=LOOKUP(2,1/(B2:B7>0),$A$2:$A$7)}
This can be copied over

For A14 another VLOOKUP
=VLOOKUP(B13,$A$2:$C$7,MATCH(B1,$A$1:$C$1,0))
This can be copied over

 

[T. Valko]

Try these...

For red bicycles

For the first date you specifically made 12 red bicycles:

=INDEX(A2:A7,MATCH(12,B2:B7,0))

If you just want to find the first date you made *any* red bicycles:

Array entered** :

=INDEX(A2:A7,MATCH(TRUE,ISNUMBER(B2:B7),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the last date you made *any* red bicycles:

=LOOKUP(1E100,B2:B7,A2:A7)

Format all of the formulas cells as Date.

Use the same technique for the blue bicycles, just change the column
references as needed.

 

[Eva]

This formula finds you first occurance.
for both formulas please enter:Ctrl+Shift+ener
=INDIRECT("c"&MIN(IF(C2:C10>0,ROW(A2:A10))))

 

[Eva]

This formula will give you the date of last occurance

=INDEX(Table,MAX(IF(C2:C10>0,ROW(A2:A10))),1)

This formula will give you the date of first occurance

=INDEX(Table,MIN(IF(C2:C10>0,ROW(A2:A10))),1)

For both formulas enter:ctrl+Shift+Enter